Access 2000 Conversion - Left(), UCase() functions

[Jackie]

I converted an Access 97 .mdb to Access 2000.<br><br>Some of the queries used the Left() function to exctract data. (i.e. IIF(Left(Organization,2) = &quot;AB&quot;,Left(Org, 2), Left(org, 4)).<br><br>The following error message occurs when the query attempts to run &quot;Undefined function &quot;Left()&quot; in expression&quot;.<br><br>A similar message is displayed when a query that employs the UCase function is run.<br><br>Both of these functions are still available in Access 2000. (I checked the help).<br><br>What is causing the &quot;Undefined function&quot; message to be displayed and is there a fix for this error?

 

[Shirley]

Have you check to make sure the references are there.&nbsp;&nbsp;While in the coding area click Tools and then References.&nbsp;&nbsp;Make sure that nothing is MISSING.&nbsp;&nbsp;If you have VB for Application, DAO 3.51 and Microsoft Access Object Library then It should work.&nbsp;&nbsp;But this is a suggestion. Because I have had this problems with early version of Access.&nbsp;&nbsp;There is a fix for early version, I'm not sure for Access 2000.

 

[Jackie]

Thank you for your response.<br><br>I folowed your instructions and the Utility.mda is marked as &quot;MISSING&quot;.<br><br>Where do I find it and tell access 2000 that it exists?<br><br>How do I tell if I have VB Application, DAO 3.51&nbsp;&nbsp;and Microsoft Access Object Library?<br><br>

 

[Jackie]

The answer to this problem was in the References.&nbsp;&nbsp;The MISSING item had to be unselected and added again.&nbsp;&nbsp;Here is what I did:<br>1. Unchecked the MISSING utility.mda item<br>2. Next I selected the Browse button <br>3. Selected the utility.mda in the Program Files/Office2k/1033 directory<br>4. Clicked OK.<br>5. Exited and re-entered the database.

 

[rbowes]

You could also make select the Microsoft DAO 3.6 library.&nbsp;&nbsp;This happened to me about 6 months ago and I was advised to add this library because it is the latest edition of DAO.&nbsp;&nbsp;If you install the SR-1 update from the Microsoft web site make sure that you run all of your apps.&nbsp;&nbsp;You will hit a similar error.&nbsp;&nbsp;The solution is to open references and select the Microsoft Office 9.0 Object Library.&nbsp;&nbsp;it is not selected by default and some vba string functions conflict with the selected vba and ado ref libraries.&nbsp;&nbsp;This will save you a great deal of time - I had the wonderful embarrassment of having my latest app crash because of this problem.