Results 1 to 7 of 7
  1. #1
    bfm is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7

    Using DLOOKUP to return a value based on matching criteria

    I have been trying to figure out how to use DLOOKUP to convert state abbreviations (ex. NY, NJ) into full state names (ex. New York, New Jersey) in a report I'm creating. I have two tables, one that has the data I'm building a report with (table name=USA), then another table that has a list of all state names and their corresponding abbreviation (table name=States). Here is what I have tried to use within the control source of my report:
    =DLOOKUP("[FullName]","[States]","[Abbr]=[USA]![State]")

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try

    =DLOOKUP("[FullName]","[States]","[Abbr]='" & [USA].[State] & "'")

    Note the use of single and double quotes and changing the bang(!) to a dot(.)

  3. #3
    bfm is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7
    I keep getting the "Enter Parameter Value" box when I run the report.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    That is telling you that whatever it is does not exist in your report - so perhaps you have not included in your recordsource, or perhaps you have a typo.

    try removing the [USA]. part

  5. #5
    bfm is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7
    That didn't work either. The field, in which I'm trying to pull up the full state name, in the report is called "State" and it's default control source is "State" from the table "USA." So how do I tell the report to use both tables to figure out that if "NY" in one, equals "NY" in the other, return "New York." Current expression that got rid of parameter pop up box, but returns #Name? where I want the full state name to appear:
    =DLookUp("[FullName]","[States]","[Abbr]='" & [Reports].[State] & "'")
    Thanks for your help...amazing how much time I can spend tweaking the same expression trying to find a solution.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you don't need [Reports]. and I presume FullName is the name of the field in States

    However normal advice would be to include your table 'States' in the record source to your report, linking on Abbr and State and pull down the FullName field onto the query grid

  7. #7
    bfm is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7
    Great idea! I'll try that next.
    Thanks so much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-29-2014, 11:20 AM
  2. Replies: 4
    Last Post: 06-30-2014, 02:53 PM
  3. Replies: 9
    Last Post: 06-28-2012, 10:10 PM
  4. Matching based on criteria from multiple tables
    By Jonpro03 in forum Database Design
    Replies: 13
    Last Post: 08-04-2011, 10:29 AM
  5. Dlookup to match two criteria and return value
    By randolphoralph in forum Programming
    Replies: 20
    Last Post: 05-20-2010, 12:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums