How to fix a "Compile error.in query expression" Error in Access 2003/XP/2000/97

Question: In 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 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.

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.