DEV ASHISH - Bugs: Undefined Function in Expression (Error 3985)

Bill Roberts

unread,

Jun 6, 2001, 1:07:51 PM6/6/01

to

On Dev's site, he points out this bug:

Bugs: Undefined Function in Expression (Error 3985)
http://www.mvps.org/access/bugs/bugs0025.htm

Can this bug also explain why calling an ACCESS query from EXCEL would
produce

Undefined function <name> in expression. (Error 3085)

As related to DLOOKUP function within ACCESS query???

Thank you,
Bill

Van T. Dinh

unread,

Jun 6, 2001, 9:38:10 PM6/6/01

to

It is possible but *more likely*, you asked DAO/ADO or Excel (without
Access)
to evaluate DLookUp which is not found of course as neither Excel or DAO/ADO
were aware of Access. Note that Access consists of basically 3 fairly
independent components, namely Access itself, DAO/ADO and VBA. If my memory
serves me right, an earlier post (from MichKa, I think??? - but if the
statement is wrong, then it is not from MichKa) stated that the ability of
DAO/ADO to interpret and refer Access inbuilt functions back to Access is
something special that only works when you "run" DAO/ADO from Access (or
something like that anyway).

Try adding the Microsoft Access Object Library to the References Collection
of your Excel workbook, then modify your Query to show

... "Access.DLookUp" ... rather than just "DLookUp"...

in your Access Query and then test it again in Excel.

I tried the DLookUp & Nz function in Excel this way (but not in a Query) and
they work correctly.

If it still doesn't work, try evaluating the Access.DLookUp function
"outside" the SQL String. This may not be possible depending on your SQL
String.

HTH & good lucks
Van T. Dinh

"Bill Roberts" <william....@exxonmobil.com> wrote in message
news:#XbI7sq7AHA.2012@tkmsftngp07...

Michel Walsh

unread,

Jun 7, 2001, 7:34:07 AM6/7/01

to

Hi,

Indeed, calling any VBA function, including user defined function (in a
standard module) within an SQL JET query is only available from within
Access. It won't work from VB, VC++, Delphi, ... neither Excel. DLookup is
not from the core of SQL but from VBA, so, DLookup won't work, neither Nz.
IIf will work, on the other hand, since JET-SQL defines iif and don't relay
on the iif as defined in VBA, same thing with some (other) arithmetic
operators.

Vanderghast, Access MVP.

"Van T. Dinh" <VanThi...@bigpond.com> wrote in message
news:uf8s6Gv7AHA.1680@tkmsftngp04...

Van T. Dinh

unread,

Jun 7, 2001, 9:23:11 AM6/7/01

to

Vanderghast

Thank you for confirming this & explaining clearly with the right
terminology
(JET vs my DAO/ADO). Before I posted, I read & re-read my post and
something bothered me but I couldn't find it which you picked up correctly
as JET rather than DAO/ADO. However, I thought the idea should come thru:
so I posted anyway.

Only 1 minor point I differ from your post: I think DLookup (and Nz) are
Access functions and not VBA functions. When I fully qualify them, I have
to use the Access Object Library rather than the VBA Library.

Many thanks again.
Van T. Dinh

"Michel Walsh" <Vande...@msn.com> wrote in message
news:OgAUOX07AHA.1896@tkmsftngp05...

> Hi,
>
>
> Indeed, calling any VBA function, including user defined function (in a
> standard module) within an SQL JET query is only available from within
> Access. It won't work from VB, VC++, Delphi, ... neither Excel. DLookup is
> not from the core of SQL but from VBA, so, DLookup won't work, neither Nz.
> IIf will work, on the other hand, since JET-SQL defines iif and don't
relay
> on the iif as defined in VBA, same thing with some (other) arithmetic
> operators.
>
> Vanderghast, Access MVP.
>
>