Microsoft KB Archive/94191

{|
 * width="100%"|

PRB: Query Error Caused by Colon in Field Name
'Article ID: Q94191

Creation Date: 29-DEC-1992

Revision Date: 19-SEP-1996' The information in this article applies to:


 * Microsoft Access versions 1.0 and 1.1

SYMPTOMS

A query results in one of the following error messages:


 * Query must have at least one output field
 * Can't set criteria unless you've specified a field
 * Syntax Error

CAUSE

One of the query's criteria includes a field name that ends with a colon. For example, the fieldname &quot;Field:&quot; used as a criteria would cause an error message.

RESOLUTION

Field names that include colons must be enclosed in brackets to be used as criteria. For example, to use the field name &quot;Field:&quot; as a criteria, you would enter it as &quot;[Field:]&quot; (without quotation marks).

STATUS

This behavior no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

In a query's Design view, you can rename a field to be more descriptive. Place the insertion point in front of the first letter of the field name in the QBE grid. Then type the new name followed by a colon. Do not delete the underlying field name. Now, in Datasheet view, you will see the descriptive label as the heading instead of the underlying field name.

Using this technique to change a field name in a query's Design view automatically changes the heading in the query's Datasheet view, and it changes the field name in a form or report based on the query. But the underlying field name in the table does not change.

The error messages shown above occur because without brackets around the field name, Microsoft Access interprets the colon in the field name as a request to add a label (rename) that field.

Steps to Reproduce Behavior


 * 1) Create a new table named Test with one field named FullName:.
 * 2) Save the table as Table1.
 * 3) Create a query based on Table1.
 * 4) Drag the field FullName: to the query grid.
 * 5) Run or view the results of the query. You will receive an error message.

REFERENCES

For more information on renaming fields in queries, search for &quot;renaming fields&quot; then &quot;Renaming a Field in a Query&quot; using the Microsoft Access Help menu.

Microsoft Access &quot;User's Guide,&quot; version 1.0, Chapter 5, &quot;Query Basics,&quot; page 96
 * }

-

"THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY." '' ©1997 Microsoft Corporation. All rights reserved. Legal Notices.

''

Additional reference words: 1.00 1.10

KBCategory: kbusage kberrmsg

KBSubcategory: QryOthr