Bugs: Undefined Function in Expression (Error 3985)
    http://www.*-*-*.com/

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

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

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.

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