Docmd.RunSQL problem... Access 2000 2

[bondm007]

I used a RunSQL statement like the following in Access97, but Access2K does not like the Left and Right functions? It says they don't exists... Is there a replacement or am I going crazy?

Docmd.RunSQL "SELECT [EN], (Left([EN],6) & Right([EN],4)) AS ESMA INTO [New Table] FROM [Old Table];"

This worked in Access97...Why does Access2K not like the Left and Right functions. Even the next statement is coming up with an error.

Docmd.RunSQL "UPDATE [New Table] SET [ESMA] = Left([EN],6) & Right([EN], 4);"


Mike

 

[jebry]

Hi Mike!

From discussions I have read in this site, it sounds like you may have a reference missing. Go to the code window and select Tools/References and see if any are marked as missing. I have only seen this once and all I had to do is recheck the box and Access found the reference, at most you may have to store a file.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[bondm007]

Thanks... I checked the references and tried unchecking and checking the Microsoft DAO 3.6 Object Library. I still get the same error.

This is the error message I am getting:

Runtime error '3085':

Undefined function 'Left' in expression.


Mike

 

[jebry]

Hi Mike!

Look for Microsoft Visual Basic for Applications Extensibility 5.3 and make sure that one is checked.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[bondm007]

Thanks.

Microsoft Visual Basic for Applications Extensibility 5.3 is the last checked item in the list. I have tried unchecking and checking it again and the same error still exists.


Mike

 

[KyleS]

Was the DAO 3.6 the only reference that came up missing?

How many references do you have marked? (and which ones are they?) Kyle

 

[bondm007]

Here they are. None of them come up as missing.


Visual Basic for Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Common Dialog Control 6.0 (SP3)
Microsoft Forms 2.0 Object Library
Microsoft Outlook 9.0 Object Library
Microsoft Excel 9.0 Object Library
OLE Automation


In that order.

Mike

 

[KyleS]

Hi Mike!
Well, the references is ALWAYS where the problm lies. Do you use the "Forms 2.0" and Common Dialog control?

If not remove whatever you don;t use. If that doesn't work add ADO 2.5 in Leave at the bottom) and see if that helps (it's the default in A 2000) Kyle

 

[bondm007]

Thanks... I'll try that on Monday.


Mike

 

[Quehay]

This is likely an ADO 2.1 vs ADO 2.5 problem. Access can assume that you've got one version when the previous is in effect. I've had the same problem. Try checking a reference to Active Data Objects 2.1 and see if this still occurs...

 

[bondm007]

Thank-you for all your help. When I converted the database from Access97 to Access2K the Microsoft ActiveX Data Objects 2.5 Library was not a reference. I have added it as a reference and placed it at the bottom of the checked list. The SQL statement now runs without a hitch...


Mike