asked on

Undefined function 'DLookup' in expression

after converting this database to Access 2007, I have run into problems with some queries and get the error "Undefined function 'DLookUp' in expression.

The SQL of this example is below (this query sets address formats, depending on the country). It works fine in Access 97, but not since the conversion.

SELECT [Client List].ClientID, [Client List].Country2, States.State, DLookUp("Value","Config","ID=36") AS Home, IIf([Country2] Is Null,[Home],[Country2]) AS DestCountry, IIf([DestCountry] Not In ("US","CA","AU","NZ","ZA","UK","IE"),[Zip2] & " " & [PAddress 3],[PAddress 3] & IIf([State2] Is Not Null,IIf([DestCountry]="UK" Or [DestCountry]="IE",", " & [State],", " & [State2]),"") & IIf([Zip2] Is Not Null,"  " & [Zip2],"")) AS Add3Alt, AddressFormat([ClientID]) AS Add3
FROM (Countries RIGHT JOIN [Client List] ON Countries.CAbbr = [Client List].Country2) LEFT JOIN States ON [Client List].State2 = States.Abbr;

Open in new window

I presume it is a reference issue.
The references used in the converted accdb are:
Visual Basic For Applications
Microsoft Access 14.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office 14.0 Object Library
Microsoft Office 12.0 Object Library

I tried removing the 12.0 Object Library, without affect.

I would appreciate any help you can provide

Scott McDaniel (EE MVE )

Are these problems on YOUR machine, or are they on a machine where you've deployed the file?

You generally don't need the "Extensibility" reference - that's normally for working with code in the VBA environment (like if you want to automate adding code to a Module).

Other than that, I don't see anything that strikes me as odd.

Try removing one of the references (remember which one it is) and then close the Reference dialog, reopen it, and then find and recheck that reference. This forces Access to rebuild the references, which can sometimes clear up that error.

CoStar

ASKER

thanks for your comments, LSMConsulting.
the problem is on my machine, which has been where the original development took place (in Access 97) and which has Access 2007 installed, to enable the conversion.
I removed one reference at a time, closed Reference control, re-opened and re-set the same reference, but there is no change in the error.
In which library is the DLookUp function normally resident?

ASKER CERTIFIED SOLUTION

CoStar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

Yep - I missed the version number. Very important to always deploy on a "lowest common denominator" platform. That is, if you must support users with Access 2007, do your development (or at least your final compile/deploy) in 2007.

CoStar

ASKER

It was discovered when comparing another converted database and a little by trial and error.