totn Access

MS Access 2003: How to fix a Compile error.in query expression Error

This MSAccess tutorial explains how to fix a compile error.in query expression error in Access 2003 (with screenshots and step-by-step instructions).

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.

Option #1

This error can occur when there is a broken Reference in your Access database and you've tried to use one of the built-in Access functions such as Left, Instr, Len, Year, Mid, etc.

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.

Microsoft Access

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.

Option #2

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:

Microsoft Access

You can fix this error by removing one of the "Option Compare Database" lines from the top of the Access module.

Microsoft Access

The error message should now disappear.