Microsoft Query is a program for fetching data from many sources, including applications in the Microsoft Office suite, dBase drivers and text files. Microsoft Query’s users perform database fetches by writing statements in structured query language, better known as SQL. Typos and lack of familiarity with SQL can cause Query to report a range of syntax errors.
SQL Error in String
Microsoft Query string errors can result if you have an incorrectly formed string, or one that’s too long. The maximum string length Microsoft Query allows is 255 characters. Writing a string for use in Microsoft Query involves putting text in quotation marks, single or double. Sometimes users mistakenly match single with double marks, or vice versa. This produces a string syntax error, since single quotes can only match single quotes, and double quotes, double. You can often find the culprit string in the WHERE clause of a SELECT query, but you may also have entered it in a pathname specifying the database you’re querying. For example, you may have typed “c:\documents\MyTable’; instead of the correct “c:\documents\MyTable”;
SQL Error in Number Type
Microsoft Query might report an SQL error in a numeric type if you’re using a string date, or some other, unrecognizable type where Microsoft Query expects a number. For example, you may write “SELECT * from MyTable WHERE OrderQuantity=’Widget’ “. The “Widget” is a text string in this example, and OrderQuantity mostly likely is a numeric field. This error can also appear if you’re trying to query with a floating point value in a field whose data type is integer. The solution here is to truncate or round up or down your number to an integer.
SQL Error in Date
SQL date errors arise when you use a date data type where Microsoft Query expects a number or a string. One approach to tracking down the error’s source is to inspect the field type of the data source you’re querying. For example, if you’re using Microsoft Query to fetch data from an Access database, open the table design window in Access. For the row displaying the field name in your query, read the correct data type in the “Data Type” column.
Errors in SQL Keywords
SQL keyword errors happen when you misspell one of the words that the SQL query language reserves for its commands and clauses. For example, writing “SLECT” instead of “SELECT” will produce this type of error. So will “JION” instead or “JOIN,” for SELECT queries pulling data from two or more tables. Check the spelling of your SQL statement to resolve this error.
It’s also possible that you’ve spelled all SQL keywords correctly, but have arranged them in an order Microsoft Query doesn’t understand. For example, Microsoft Query won’t understand the statement “from Table1 Select ” and report an SQL syntax error. Instead, write “SELECT from table 1.”