Undefined Function "Mid" in expression 1

[dpedley]

I have a query, which works fine on my pc, which uses the mid function to parse data. When I access the db from another pc I get the message (Undefined function "mid" in expression). Even though this is a standard function, I have checked all the references and everything is the same between my pc and the others.

Oddly if I create a dummy db on these same pcs and use the mid function in queries it works just fine. I've never run into something this strange before. Any ideas?

 

[SkipVought]

Hi,

You might be missing a reference.
[tt]
In Access Help - Search for 'Missing Reference' :

Troubleshoot undefined functions and references to projects or libraries
Show All
Hide All
When you open a Microsoft Access file that contains references to projects or libraries that aren't in the location specified in the References dialog box, Access tries to find the projects or libraries. However, if Access can't find a referenced file, you might have to set the reference manually. You might not be aware that you need to fix a missing reference until you receive an "undefined function" message or a message that Access can't find a project or library when you try to run a query or a Microsoft Visual Basic procedure (procedure: A sequence of declarations and statements in a module that are executed as a unit. Procedures in Visual Basic include both Sub and Function procedures.). To fix a missing reference, open a module in Design view and click References on the Tools menu. Then clear the check box next to any missing references and set the references you want.
You can't call a class module from a query, form, report or macro. Store a Visual Basic procedure in a standard module if you need to call it from a query, form, report, or macro.
You might receive an "undefined function" message when you open a data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) outside of Access if the data access page contains a function that isn't available outside of Access.
[/tt]

Skip,

_[red][/red]

 

[dpedley]

Thanks Skip, but as I mentioned I checked all the references already. Also, By process of elimination, the two which seem to be the ones affecting this (VBA and MSAccess 11)can't be unchecked while in tools reference because it says they are open, and the only way I know of to get to the references is while in a module.

 

[PHV]

Does the DB compiles properly ?
menu Debug -> Compiles ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpedley]

Unfortunately, yes it compiles just fine. This is just really odd to me. Especially considering that the function does work in the dummy db I created. It appears to only be a problem with this db, and I can't find anything different between my pc and the others.

 

[PHV]

Try to open the DB with the /decompile command line option

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpedley]

I'm not sure how to do this. If you are referring to opening the app from Command Line prompt. We can't do that here. The ability to reach a Dos prompt, has been restricted. If there is another way, please let me know.

 

[PHV]

If there is another way
A shortcut.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpedley]

Thanks, but that did not work either. I'm still getting the Undefined message.

 

[PHV]

What was the properties of your shortcut ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpedley]

Target: "C:\Document and Settings\Desktop\SecureidFobs.mdb" /decompile
Start: "C:\Document and Settings\Desktop"

Run Normal Window

 

[PHV]

Target: "\path\to\msaccess.exe" "C:\Document and Settings\Desktop\SecureidFobs.mdb" /decompile

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

The full steps:
start the DB with /decompile
compact and repair
compile the VBA
compact again
save and quit
open the DB normally and test your query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dpedley]

First. Sorry I didn't get back to you on Friday. I ended have to leave the office around 3:30, and we had 5 people not come in this morning, so I just got a chance to try this.

Second. Thank you so much. This has appeared to do the trick. Everything seems fine. Again thanks so much for sticking with me on this.