Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 28th, 2003, 08:57 AM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default Undefined function 'Left' in expression

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
 
Old July 28th, 2003, 09:04 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 28th, 2003, 09:20 AM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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;)

 
Old July 28th, 2003, 09:30 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old July 28th, 2003, 09:51 AM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 28th, 2003, 10:00 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old July 28th, 2003, 04:59 PM
Registered User
 
Join Date: Jul 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old July 30th, 2003, 03:44 AM
Authorized User
 
Join Date: Jul 2003
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old July 30th, 2003, 08:04 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 29th, 2004, 02:23 PM
Registered User
 
Join Date: Oct 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Undefined function 'Left' in expression MountainProud Access 4 October 23rd, 2006 10:48 AM
undefined function vurtman ASP.NET 1.0 and 1.1 Basics 8 October 4th, 2004 01:32 PM
Undefined function dhborchardt Classic ASP Databases 0 June 7th, 2004 03:31 PM
Undefined functin left greenfish Access 2 March 24th, 2004 11:51 AM
undefined function: mysql_connect() shabboleth Beginning PHP 2 September 18th, 2003 05:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.