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

ID: Q142998

The information in this article applies to:

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

for example:


   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

Create the following new table:


       Table: a - b  (make sure to include the spaces in a - b)
       --------------------------------------------------------
       Field Name: Test
          Data Type:  Yes/No

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

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.

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.

Run the query. Note that it runs correctly.

Close and save the query with any name.

Open the query in Design view and make the following change:
```
       Update To: Yes 
```

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

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

Click Cancel and reopen the query in Design view.

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.

Additional query words: dash dashes

Keywords : kbusage QryMktbl
Version : 1.1 2.0 7.0
Platform : WINDOWS
Issue type : kbbug