Syntax Error

[abriscoe]

I have several Access 2010 queries, that when I open them in datasheet view and click Sort on the column header it gives me "Syntax error(missing operator in query expression" warning. I click OK and the alpha sort drop down then appears but the box for selecting or deselecting does not appear below the text filters. Some queries work fine and they are all running off the same tables. Any ideas on how to correct?

Thanks.

[boblarson]

Post the SQL for one, or more, of the queries involved.

[abriscoe]

Below is the SQL for the one I am having the most problems with.

SELECT [tbl Audit Status].Active, [tbl Audit Status].[Physician Number], [tbl Audit Status].[Provider Name], [tbl Audit Status].[Audit POS], [tbl Audit Status].[2011 Audit], [tbl Audit Status].[2011 Risk Level], [tbl Audit Status].[2011 Auditor], [tbl Audit Status].[2011 Re-Audit], [tbl Audit Status].[2011 Re-Audit Risk Level], [tbl Audit Status].[2011 Auditor (Re-Audit)], [tbl Audit Status].[2010 Re-Audit], [tbl Audit Status].[2010 Re-Audit Risk Level], [tbl Audit Status].[2010 Auditor (Re-Audit)]
FROM [tbl Audit Status]
WHERE ((([tbl Audit Status].Active)="a") AND (([tbl Audit Status].[Audit POS])="op"))
ORDER BY [tbl Audit Status].[Provider Name];

[boblarson]

I could be wrong (it has been known to happen ) but I'm wondering if you are having problems because of your field names having the special character - (dash) or parens () in them. Spaces and special characters are wickedly a pain to have in field or object names. It is best to not use them. So like 2010ReAudit would be better than 2010 Re-Audit. And I'm just thinking that, even though you are using square brackets around the field names the operation you are dealing with is not able to use them the way they are.

I guess the test would be to remove the WHERE clause to see if it still does it. If it does then that is a good chance of being the culprit. If it works when the Where clause is removed, then it might have to do with the items you have. Also, take out the Sort as a test too (but do things one at a time so you can narrow the focus down).

One other thing that may be needed is a DECOMPILE. That will sometimes fix things that just have no other explanation. So, you might try that. You can find instructions for that here:
http://www.granite.ab.ca/access/decompile.htm

[abriscoe]

Do you know if there is a way to make these changes to the tables and have it automatically make the changes to the queries and reports? Otherwise I have to go back through and make all the changes to the queries and the reports. I tried the decompile and that did not seem to work. I also tried taking out the where clause and it did not work and then took about the Sort and again it did not work. It is odd because sometimes it will work and other times it will not.

[boblarson]

You can use the free V-Tools which has a tool called TOTAL DEEP SEARCH and that can do replacements (make sure you turn Name AutoCorrect off in the options of your database though. It can do funny things including corrupt things).