Compile error on common function, NOT REFERENCE PROBLEM

[rgrantz]

Strange issue:

My "mid" function works in module windows (I can write a procedure that
returns a message box whose prompt is text looked up by the Mid function),
but when using Mid in the query builder window in the criteria cell, I get a
compile error.

I've only been able to find one proposed solution for this Access function
compile error on the 'net: Do the unchecking Missing References thing, etc.
I have no missing references, I have tried the unchecking all I can and then
rechecking them, I've tried adding a bunch, I've tried taking a bunch out
(no problems), but the Mid function (whose syntax is correct) always returns
the "Compile error. in criteria expression" error (and yes, there is that
period in the error message).

Is there some way to completely reset the VBA reference, or all references
(can't check/uncheck vba or DAO w/ access open)? Is there some weird
problem when Mid works in a module but not in the query criteria?

Using Access 2000 from Office 2000 Premium edition, when installed chose
"Run all from my computer", all on Windows 2000 Professional.

Any help is appreciated, thanks.

 

[Alex Ivanov]

Have tried to repair and compact the database? If that does not work, try to
import all your objects into a blank database.
You may try to install the latest service pac or preferably upgrade to newer
version of Access.
IMO Access 2000 is the worst version released by MS.

 

[Peter R. Fletcher]

There are some Functions that you can't use directly in criteria (it
depends on the context in which they are being evaluated) - Mid may
well be one of them. Try creating a calculated column for the
expression containing Mid and put the appropriate comparison with that
column in your criterion cell.

Strange issue:

My "mid" function works in module windows (I can write a procedure that
returns a message box whose prompt is text looked up by the Mid function),
but when using Mid in the query builder window in the criteria cell, I get a
compile error.

I've only been able to find one proposed solution for this Access function
compile error on the 'net: Do the unchecking Missing References thing, etc.
I have no missing references, I have tried the unchecking all I can and then
rechecking them, I've tried adding a bunch, I've tried taking a bunch out
(no problems), but the Mid function (whose syntax is correct) always returns
the "Compile error. in criteria expression" error (and yes, there is that
period in the error message).

Is there some way to completely reset the VBA reference, or all references
(can't check/uncheck vba or DAO w/ access open)? Is there some weird
problem when Mid works in a module but not in the query criteria?

Using Access 2000 from Office 2000 Premium edition, when installed chose
"Run all from my computer", all on Windows 2000 Professional.

Any help is appreciated, thanks.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Tim Ferguson]

IMO Access 2000 is the worst version released by MS.

That's because you never used Access 95, or Access 1.0....


Tim F

 

[Wayne Morgan]

Please copy what you've done and paste it in a message so that we can see
what we're working on. Also, let us know where you have located what you
post. Is it the SQL in a stored query or is it in code?

 

[Alex Ivanov]

That's because you never used Access 95, or Access 1.0....
You are right.
I've used Access 2, 97, 2000 and 2002.
And I'm still using 97 if that matters.

 

[rgrantz]

I attempted to put the expression

mid([FieldName],3,2)="ZZ" in the criteria cell for [fieldname] in the Query
Builder (not SQL view). When running the query, I get the immediate
"Compile error. in criteria expression" message. When searching newsgroups,
I found that the advice most commonly given for this problem was to check
the references in Tools-->References, check for missing ones, remove ones,
add them again, etc. etc. I've tried removing all ones possible (Visual
Basic and DAO Objects say they can't be removed because they are in use),
then adding them back, not adding them back, etc. etc. I have made a new
blank database and imported all objects from the old one, I've compacted and
repaired. I've also removed and reinstalled Office 2000 Premium and
installed all Office Updates.

To make sure the visual basic function works, I made a module with:

Sub midtest()
Dim strtext As String
strtext = "thisisatest"
MsgBox Mid(strtext, 3, 2), vbOKOnly
End Sub

Running this sub returns a message box with prompt "is", which shows, I
believe, that the mid function operates.

Is anyone able to put a Mid function in their criteria in the query builder?
Is it possible that it's simply not something that can be used there?


Thanks

 

[Douglas J. Steele]

It's possible that Jet is now operating in Sandbox mode. Check article:

http://support.microsoft.com/kb/239482

I'd suggest that, following the instructions in that article, you set the
registry key

\\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\engines\SandboxMode

to 2 (use sandbox mode for non-Access applications, but not for Access).

 

[Alex Ivanov]

Try to run Query1 in this sample. Does it work?

http://www.aleksoft.net/samples/TestMid.mdb

--
Please reply to NG only. This email is not monitored.
Alex.

I attempted to put the expression

mid([FieldName],3,2)="ZZ" in the criteria cell for [fieldname] in the
Query
Builder (not SQL view). When running the query, I get the immediate
"Compile error. in criteria expression" message. When searching
newsgroups,
I found that the advice most commonly given for this problem was to check
the references in Tools-->References, check for missing ones, remove ones,
add them again, etc. etc. I've tried removing all ones possible (Visual
Basic and DAO Objects say they can't be removed because they are in use),
then adding them back, not adding them back, etc. etc. I have made a new
blank database and imported all objects from the old one, I've compacted
and
repaired. I've also removed and reinstalled Office 2000 Premium and
installed all Office Updates.

To make sure the visual basic function works, I made a module with:

Sub midtest()
Dim strtext As String
strtext = "thisisatest"
MsgBox Mid(strtext, 3, 2), vbOKOnly
End Sub

Running this sub returns a message box with prompt "is", which shows, I
believe, that the mid function operates.

Is anyone able to put a Mid function in their criteria in the query
builder?
Is it possible that it's simply not something that can be used there?


Thanks


message

 

[Wayne Morgan]

Doug,

This may be worth checking, but the function causing the problem (Mid) is
listed as one of the allowed functions in Sandbox Mode. However, it says
that Sandbox mode was "more restrictive" prior to Jet 4 SP 8, but doesn't
say what those additional restrictions are.

 

[Wayne Morgan]

I just tied the Mid in a query to make sure and it worked. Here is the SQL
from my test.

SELECT Table10.FirstName, Table10.Middle, Table10.LastName, Table10.Suffix
FROM Table10
WHERE ((Mid([Middle],3,2)="ex"));


Doug brought up a good point about Sandbox mode. Do you have the current
service packs installed for Office, Jet, and MDAC? You don't mention your
operating system, if it is Windows 2000 or Windows XP, you can get the
latest Jet service pack by running Windows Update. If not, go to
http://msdn.microsoft.com/data/downloads/updates/default.aspx.

Will you paste the SQL of your query into a message?