mid function error

[Guest]

I am having a very frustrating time trying to figure out why I am getting an
error message when using the Mid function. The error message says that my
database can’t find the field Mid referred to in my expression. Yet when I
copy and paste the same Mid statement line into the debug window it gives me
the correct results.
I tried a simple test sub such as:
Dim varTest as Variant
varTest = Mid(“abcdefghijâ€,2,6)
Debug.print varTest

It works fine when I have it in a standard module, and call it (using a
command button onclick event), but when I have it in my form's module and
call it (the same way & with the sub in the standard module removed) I get
the error message.

Is there a reference library I am missing? I don’t even know what they
are, but the ones I have checked are: Visual Basic for Appl., MS Acess 10.0,
OLE Automation, MS DAO 3.6, MS ActiveX Data Obj. 2.1, MS Office 10.0 Obj. Lib.

 

[Allen Browne]

The problem does sound like either a naming clash or a References issue.

When you choose References from the Tools menu (in the code window), none of
those have the word "MISSING" beside them?

Is there anything else within the context of this code that has the name
Mid? A text box? Field? Module? Form? Function? Project?

Can you work around the problem by specifying you want the Mid() function
from the VBA library:
varTest = VBA.Mid("abcdefghij",2,6)

You could also try these fairly generic corruption repair steps too:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

 

[Vsn]

you might try to send a NULL value from your form or a string value which is
shorter as 7 at start up of the form.

try to use mid(nz(YOUR STRING VALUE,space(7)),2,6)

what do you think?

Ludovic

 

[Guest]

I checked all over and couldn't find a naming clash. I performed all the
procedures you recommended. The only one that worked was preceding the Mid
with VBA. I can certainly live with that. It's still a puzzle as to what is
happening, but I want to thank you for the time and effort and for giving me
a work-around.

 

[Guest]

Thank you for your suggestion. The code I displayed was just an example.
The actual application of the Mid function is more involved, getting its
string argument in different ways depending on the routine. Again, thanks.