ACC: Query on Table with Space-Hyphen-Space Table Name Fails

• Microsoft Access versions 1.1, 2.0, 7.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you use an update query, Microsoft Access will prompt you for a parameter value if the table you use in the query was created with the following naming convention:

   Text space hyphen space Text

   Training - Upgrade

CAUSE

The update query removes the spaces in the table name from the SQL statement if you open the query in Design view and then resave it.

RESOLUTION

To resolve this problem use one of the following workarounds:

• Do not create tables names that contain space hyphen space.
• Once you create the query and run it, do not save the query. Or if you save the query and then run it, do not modify the query; instead, create a new query.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 1.x, 2.0, and 7.0. This problem no longer occurs in Microsoft Access 97.

MORE INFORMATION

Steps to Reproduce Problem

1. Create the following new table:

   Table: a - b (make sure to include the spaces in a - b) -------------------------------------------------------- Field Name: Test

             Data Type:  Yes/No
   
   

2. Save and close the a - b table (no need to define a primary key), and then open the table in Datasheet view.

3. Add five records to the a - b table with a value of Yes. (In Microsoft Access 7.0, click to select the box that appears in Datasheet view to set the record to Yes). Close the a - b table.

4. Create the following new update query based on the a - b table:

   Field: Test Table: a - b Update To: No

   NOTE: In Microsoft Access version 1.x or 2.0, if the Table row is not displayed in the QBE grid, on the View menu, click Table Names to display the Table row.

5. Run the query. Note that it runs correctly.

6. Close and save the query with any name.

7. Open the query in Design view and make the following change:

   Update To: Yes

8. Close the query and click Yes to the save the changes.

9. Run the update query again. Note that you are prompted to enter a parameter value.

10. Click Cancel and reopen the query in Design view.

11. On the View menu, click SQL. Note that the spaces have been removed from the table name in the SQL statement.

REFERENCES

For more information about update queries, search on the phrase "Update Queries," and then view the "Run a Make Table, Append, Delete or Update Query" topic, using the Answer Wizard from the Microsoft Access for Windows 95 Help menu.