Hi All,
This is something that seems to be happening more frequently and it's becoming a real pain. In a query, if I put something like the following in for a calculated field:

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

I get the error message "Undefined function 'Left' in expression".

I had this happen recently on Access 97 on a Windows 98 machine then just now on a different file in Access 2002 on a Windows 2000 machine.The databases seem to be OK for a while then this kicks in. Anybody any thoughts as to why? Any cures? Am I missing something?
Cheers
M

Are you using code to designate what Utility.mda to use? If so, check your reference to the Utility.mda. Go to a module and open in Design View. From the menubar, click Tools > References. A list box should pop up. Scroll till you see the Utility.mda.

I always had an issue with this when running more than one version of Access on one pc. Our app had code specifying the path for the Utility.mda. Access by default will find the Utility.mda, so I recently deleted the code, removed the reference and it's a better world now.

HTH,

Beth M

Hi Beth,
I don't think I was (sorry for being a bit dim).
All I'm doing is querying a very large table in the same database that lives on a networked server. I've not had to use any code so far as I mostly only run ad hoc queries. I looked in the references but didn't see any Utility.mda. Again, am I missing something?(Polite replies only please;)

Try setting a reference to the Utility.mda in the way which I previously described.

Create a new module and open in Design View. From the menubar, click Tools > References. A list box should pop up. Scroll till you see the Utility.mda. Mark the check box beside it. Click OK and try to run your query again.

Let me know what happens.

Thanks for your time Beth.

Have been looking in References and still can't see anything resembling Utility.mda. All I've got checked are:
VB for Applications
MS Access 10.0 Object Library
MS Excel 10.0 Object Library
MS Office 10.0 Object Library
OLE Automation
MS VB for App Extensibility 5.3

I'm only a novice at programming so wouldn't recognise if a reference was called by another name. Could that be the case?
I'll be leaving work soon so if I don't answer to your reply I'm not being ignorant - just home.
Thanks for your patience.
M

Click the Browse button to find the Utility.mda. It all depends on what version you are using, but it usually can be found in the C:\Program Files\Microsoft Office\Office (and then in the 1033 folder if you use the English version) and you'll need to change the files of type to look for the *.mda files.

The problem seems to ring a bell. Looked it up quickly in Technet, but don't seem to see it. And of course it should work, and does if you set up a new local database with one table with just that field and the query. I don't think it is related to the Utility add-in (Utility.mda).
Have you tried using Left$() in place of Left()?
btw, I don't think it is a stupid question. Weirder stuff than this happens all the time with Access. Knowledge Base gets to be your friend.

Hi All,
Had already tried Left$() and one or two other things to no avail but...

Beth, you came through in the end! May blessings be poured upon you and may you always be online when I'm stuck.;)
Located the Utility.mda like you said and the query ran OK.
Went back to the old 97 version which still had some queries I needed and did the same - it ran fine.

Need to go back to my Access books and see what I've missed regarding references and add-ins etc.

Cheese!Made my day/week/month
M:D

Hey Morris,

I'm glad you got it working! And thanks for the blessings! I'll take all I can get :)

Have a good one,

Beth M

Hello,
I am having the same problem with "Undefined Function Left in Expression" error on my Access 2000 DB.
I have done both checking Utility.mda in references and
making sure
Visual Basic for Applications
Microsoft Access 9.0 Object Library
references were selected and available.

Still, I can't run my query with Left function in it. I get "Undefined Function Left in Expression" message.
One thing that is interesting is that there is at least 5 or 6 "Visual Basic for Applications" under references.
The one that was checked by default I can't UN-check. The rest I can't select, because they all have the same names. I don't know which one is the right one anyway...

I don't have enough knowledge to tackle this on my own. I hope someone has a good advise. Any suggestions are greatly appreciated.
Thank you!