Morris & Paumir-

You'll also get this error if your project isn't compiled and you have an error in your code. Open any module in the Visual Basic Editor and choose Compile from the Debug menu. Checking Tools / References is also a good idea. You typically don't need the Utility library (an artifact in a database converted from Access 97 or earlier). Don't dink with the VBA library - the one checked at the top of the list is usually the correct one for your release. If you have any marked "Missing", uncheck those and try to compile the project.

If all this fails, create a new database and import all the objects from the old one to get rid of any corruption.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

I had the same problem with "Undefined Function Left in Expression" error in my Access 2003 database when opening a particular form. The underlying query was fine. It was the form itself which was choking when opening it. I didn't use the LEFT function anywhere in this particular form so it was driving me nuts. Oh, and the problem only occurred for 2 out of 20 users using the same database.

J Lovell's suggestion to uncheck any missing references (VBA -> Tools -> References) worked for my particular problem (at least for one user it did. I'm still waiting to test the other user)

Just for another two cents here, I find I sometimes need to do this in the query builder:

PrimeDiag:(Left([FCE Primary Diagnosis],5))

HTH


mmcdonal

Look it up at: http://wrox.books24x7.com

Hi,

This is my observation to the issue:
I made some testing with Office 2007 to older Access files (2000).
Did not save the old, but just converted to 2007 version and saved it.
I got the same "Undefined Function Left (Mid, Right)" when trying to start with older Access.
In this case also Outlook library is chosen and it seems that 2007 changed the library to ver 12 (from ver 9) and this halted my queries and modules.
I had to go to Tool-References and tick-Off Outlook ver 12 and tick-on Outlook ver 9.

So be cautious if you try new Office version to older programs that are still in production.
My advice is take to a copy with older software first and test that copy only with your new Office programs.

Have a nice summer.
Axxess