Question: In Microsoft Access 2003/XP/2000/97, I tried using the Left function in a query and I get the following error:
"Compile error.in query expression"
I've seen this function work in other Access databases. How come it does not work in mine?
Answer: This error can be caused by multiple scenarios. Let's look at two of the most common causes.
To correct the problem, you need to open your Access database. Press Alt-F11 to open the Microsoft Visual Basic window. When the Microsoft Visual Basic window appears, select References under the Tools menu.
When the References window appears, check to see if there are any References checked that start with "MISSING:". If there are any, uncheck these References. Then click on the OK button. In this example, we want to uncheck the reference called "MISSING: Microsoft Word 9.0 Object Library".
If you, in fact, need this Reference, go back to the References window (Tools > References) and re-check the reference. Click on the OK button.
Now your Access database should have fixed the broken references and the error message should disappear.
In the event that there were no References that start with "MISSING:", uncheck one or more of the existing References. Make sure that you record the References that you are unchecking. Then click on the OK button. Then go back to the References window (Tools > References) and re-check the references that you just unchecked.
Access will update its references and the error message should now disappear.
This error can also be the result of a module in your Access database having two lines with "Option Compare Database" at the top of the module. For example:
You can fix this error by removing one of the "Option Compare Database" lines from the top of the Access module.
The error message should now disappear.
(scroll to see more)