I've inherited a MS Access database which I should batch-update some data on. Thus, I've created a new query and as a first test tried to get some filtered record list - without success. Access strictly refuses to compile code that contains the LEFT function.
This does compile:
SELECT ColPath FROM MyTable;
This does not compile:
SELECT LEFT([ColPath], 3) FROM MyTable;
Even a simple
SELECT LEFT('Hello', 2);
doesn't work.
I've googled a lot now, and found solutions that either recommend checking the references in the Tools/References dialog in VBA view. There a no missing references in my case. A second solution was to check VBA modules for duplicate OPTION COMPARE DATABASE statements - none in my case.
I then created a brand new database and tried - surprisingly, everything works fine! I now compared the references of the new database to the old one: They are the same.
I'd be happy about any ideas on this...
Sounds like you messed up your references.
In the VBA editor, go to Tools, then References.
The top 2 should always be Visual Basic For Applications, then Microsoft Access ##.# Object Library, in that order (note the priority buttons to change order). Anything else will cause trouble.
Even though you have no missing references, incorrect ones can still cause this issue.
Second to that I'd do the general troubleshooting steps, decompile (Win R, MSACCESS.EXE /decompile, Open the database, hit Debug -> Compile) and a compact and repair. That will cause your entire database to recompile, and if your VBA code contains compile errors, that'll affect any queries calling any function.