The function you entered can't be used in this expression -Error message

[jebert]

I am supporting a Access97 database using forms & queries to select
data from tables. I have successfully re-compiled this program in
Access 2000 without errors and this mde file worked fine in Access 2000
& 2002.

Certain data input forms no longer work, returning the following
message:

The function you entered can't be used in this expression. Followed by
You may have used a DoEvents,LBound,UBound,Spc or Tab function in an
expression. You may have used an SQL aggregate function, such as
Count, in a design grid or in a calculated control or field.

This is not effecting all forms, the database opens OK, table and data
display forms are all working fine.

I thought it may be a problem with 2003, however the user having the
problem (I can duplicate it) was not running Access at all and is using
the Access97 runtime I bundle with every database install. Could this
be a problem with later MS Office or MS Windows versions?

Help...

 

[Michel Walsh]

Hi,


That is probably due to extra security. You cannot execute some functions
directly from the query, such as SHELL(), as example. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;294698&Product=acc

It can be something else, but if it is the case, then defining your own VBA
function, such as MyShell(), that does nothing else than calling the now
illegal function, Shell, in my example, and use MyShell(), your VBA
function, instead of Shell(), in the SQL query.


Hoping it may help,
Vanderghast, Access MVP

 

[jebert]

Thanks,

However, I I am not certain this is the issue. I Went into
Tools/reference and unchecked a Missing reference " Acrobat Cotrol for
ActiveX" . This fixed the queries problem, as well as a Date() problem
I was having.

However, it created a new problem as I do require the Acrobat reader
ActiveX control as the database hyperlinks to over 10,000 pdf images
and the ActiveX control automatically launched Acrobat Reader when the
user clicked on the button for the image to be displayed. This no
longer works effectively.

I have tried adding the reference back in but it does not seem to work.

Any ideas

Jim

 

[Michel Walsh]

Hi,



If the word MISSING was not starting the description of the reference, then
unchecking it was unlikely to solve a problem of reference (but could solve
a problem of ambiguity of a name found in more than one reference). If there
is ANY CHECKED reference with the word MISSING in its description, try to
uncheck those.


Note also that some function cannot be use in design table for validation
rule, as example, but that is not new to new Access versions.


Hoping it may help,
Vanderghast, Access MVP