Microsoft KB Archive/142998

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

Article ID: 142998

Article Last Modified on 5/7/2003

-

APPLIES TO


 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q142998



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



SYMPTOMS
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.



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.</li>  Create the following new update query based on the a - b table:

<pre class="fixed_text">      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. </li> Run the query. Note that it runs correctly.</li> Close and save the query with any name.</li>  Open the query in Design view and make the following change:

<pre class="fixed_text">      Update To: Yes </li> Close the query and click Yes to the save the changes.</li> Run the update query again. Note that you are prompted to enter a parameter value.</li> Click Cancel and reopen the query in Design view.</li> On the View menu, click SQL. Note that the spaces have been removed from the table name in the SQL statement.</li></ol>

<div class="references_section">