Mid Function in Access 2003 after 97 conversion

[Guest]

I converted an Access 97 database to Access 2003 and get an error that there
is an "Undefined Function 'Mid' in Expression". This also happens when I try
to use the Expression Builder to create a simple Mid function in a Query. It
worked fine in Access 97 and seems to be a valid function in Access 2003.
Any Suggestions?

 

[Douglas J. Steele]

Your References collection is probably messed up.

Go into the VB Editor and selecet Tools | References from the menu bar.

Examine all of the selected references (they'll all be at the top of the
list).

If any of the selected references have "MISSING:" in front of them, unselect
them and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by compiling your application, through the
Debug menu), go back in and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected.\

(NOTE: write down what the references are before you delete them, because
they'll be in a different order when you go back in)

 

['69 Camaro]

Hi, Scott.

I converted an Access 97 database to Access 2003 and get an error that
there
is an "Undefined Function 'Mid' in Expression".

You're most likely missing a library reference after the conversion. Open
the VB Editor and open the References dialog window by selecting the
Tools -> References menu. Remove any "MISSING:" References and then add the
same named References with the correct paths. Close the References dialog
window and attempt to recompile by selecting the Debug menu -> Compile
<DatabaseName>. Fix any errors, then attempt to recompile the VBA code
repeatedly until the code compiles without errors. Once it compiles
without errors, your References Collection should be complete.

If you don't see any "MISSING:" References listed, then add another library
reference (any one that isn't already listed), close the References dialog
window and attempt to recompile, then open the References dialog window
again and remove the library reference you just added. Close the References
dialog window and attempt to recompile again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Thank you. That solved the Mid problem.

It did not solve another problem that appeared after the conversion. I need
to get the highest batch number in the table and wrote the following to do so:

Set dbCurrent = CurrentDb
strSQL = "SELECT batch FROM gltran WHERE ( ( gltran.Journtype = 'gj' ) )
ORDER BY gltran.Batch DESC;"
Set rsmax = dbCurrent.OpenRecordset(strSQL)

With rsmax
If Not .EOF Then
rsmax.MoveFirst
intGJMax = rsmax.Batch
Else
End If
End With
Field8 = intGJMax

It stops running the code at intGJMax = rsmax.Batch and says Method or data
member not found. Can you help with this?

 

[Douglas J. Steele]

I'm guessing you've declared rsmax as

DIm rsmax As Recordset

While that was fine in Access 97, Access 2003 has references set to both ADO
and DAO, with the ADO reference (unfortunately) being higher in the sequence
so that it takes precedence.

The quickest fix is to change your declaration to

Dim rsmax As DAO.Recordset

Alternatively, if you're not planning on using ADO, you can go into the VB
Editor, select Tools | References from the menu bar and unselect the
reference to Microsoft ActiveX Data Objects 2.1 Library. (To be honest, I
always use the explicit declaration style, even when coding in Access 97)

 

[Dirk Goldgar]

In

intGJMax = rsmax.Batch

That should be:

intGJMax = rsmax!Batch

Are you saying that it worked before, with the dot instead of the bang?
I'm very surprised.

 

[Douglas J. Steele]

Oops. Misread your problem. Dirk's got the correct answer.

 

[Guest]

Thanks. That solved the problem and, yes, it did work before without fail.

BTW, I find this forum (i.e., you guys who respond to questions) fantastic.
I have rarely not recieved a reply that was right on! Thanks.

 

[Dirk Goldgar]

In

Thanks. That solved the problem and, yes, it did work before without
fail.
Interesting.

Thanks.

Glad to help.