• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1014
  • Last Modified:

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
0
CoStar
Asked:
CoStar
  • 3
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
CoStarAuthor Commented:
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?
0
 
CoStarAuthor Commented:
The problem has been solved. The two libraries containing "14.0" should be replaced with their equivalent "12.0". After that change all common functions like "DLookUp" worked fine.

The full and correct list of references for an Access 2007 database should be

  Visual Basic For Applications
  Microsoft Access 12.0 Object Library
  OLE Automation
  Microsoft Visual Basic for Applications Extensibility 5.3
  Microsoft Office 12.0 Object Library

Perhaps this will help others.
Thanks for your input.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
CoStarAuthor Commented:
It was discovered when comparing another converted database and a little by trial and error.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now