Undefined function 'DLookup' in expression

Posted on 2012-09-20
Last Modified: 2012-09-25
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
Question by:CoStar
    LVL 84
    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.

    Author Comment

    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?

    Accepted Solution

    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.
    LVL 84
    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.

    Author Closing Comment

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now