MSQUERY: "Syntax Error" Occurs Using Field Name Aliases

ID: Q134685

The information in this article applies to:

Microsoft Query for Windows, version 1.0
Microsoft Query for Windows 95, version 2.0

SYMPTOMS

In Microsoft Query, you may receive an error message similar to one of the following when you run a Structured Query Language (SQL) SELECT statement from the Execute SQL dialog box

Syntax Error.
SELECT <tablename>.<fieldname> '<aliasname>'<< ??? >> FROM <tablename>

where:

<tablename> is the name of the table being used.
<fieldname> is the field name.
<aliasname> is the alias name for the field name.

-or-

Syntax error in query expression.

CAUSE

This behavior occurs because Microsoft Query has its own parse engine. This parse engine processes SQL statements for database management systems (DBMSs), or back-ends, that do not support certain SQL syntax. When you run a query via the Execute SQL dialog box, it bypasses the Microsoft Query parse engine and sends the SQL statement directly to the Open Database Connectivity (ODBC) Driver Manager. If the ODBC Manager does not understand the syntax of the SQL statement, it passes it on to the DBMS. Database back-ends, such as Microsoft SQL Server, that can process SQL statements in this manner will have no problem accepting them. However, with other DBMS, such as dBASE, this situation will cause an error and you must use the alternative method described in the "Resolution" section of this article (below).

RESOLUTION

To resolve this situation, use syntax similar to the following example when you run a query from the Execute SQL dialog box:


   SELECT customer.CUSTMR_ID AS "Customer Id" FROM customer

MORE INFORMATION

Field name aliases let you provide a different name for a field heading. For example, to return the CUSTMR_ID field from the CUSTOMER table as "Customer Id," Microsoft Query creates a SELECT statement as follows:


   SELECT customer.CUSTMR_ID 'Customer Id' FROM customer

NOTE: If you use this statement in the Execute SQL dialog box in Microsoft Query, you will receive the error messages described in the "Symptoms" section of this article.

REFERENCES

For more information on creating a query using the Execute SQL command:

Search for "Execute SQL Command" using the Help menu.

Additional query words:

Keywords : kbinterop kbtool xlquery
Version : 1.0 2.0
Platform : WINDOWS
Issue type :