Results 1 to 5 of 5
  1. #1
    jarvisaurus is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    3

    Dlookup


    Using a query, I'm trying to perform a dlookup expression in my "description" field using the following:

    Description: DLookUp("[Description]","Plan Codes","[Pay Plan]=Table![Plan Codes]![Plan Code]")

    I want to use the description values from the "Plan Codes" table. It gives me an error that Pay Plan cannot be found....

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Can you explain in a little more detail what you are attempting?

    And - perhaps it will help if you post the SQL of your query here.

  3. #3
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    I believe you need to specify a distinct value for the criterion (the last field) rather than just point at a field. For instance, to retrieve the [Description] from "Plan Codes" where [Plan Code] is equal to, say, "Primary" and assign it to the string variable strDesc, you'd use something like:

    Code:
    strDesc = DLookup("[Description]","Plan Codes","[Pay Plan]= 'Primary'")
    If your [Pay Plan] value is displayed in a text box named txtPayPlan, you'd use:

    Code:
    strDesc = DLookup("[Description]","Plan Codes","[Pay Plan]= '" & txtPayPlan.Value & "'")

  4. #4
    jarvisaurus is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    3

    Dlookup

    Sorry I'm fairly new to Access, I was trying to reference another table to fill in the descriptions for "Plan Code" using a query. I thought that using the query would do the trick and I thought Dlookup was like a Vlookup in excel.

    Below is the SQL Statement:

    SELECT PSPY.[Pay Ins Cd], PSPY.[Pay Ins Plan],[PSPY]![Pay Ins Cd] & [PSPY]![Pay Ins Plan] AS [Pay Plan][Plan Codes].Description, [Plan Codes].Payor
    FROM PSPY LEFT JOIN [Plan Codes] ON PSPY.[Pay Plan] = [Plan Codes].[Plan Code];

  5. #5
    jarvisaurus is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    3

    Solved

    I just created another query to reference that particular field. I think the field was not being calculated because the other field was already a calculated field...Creating another query for those plan codes and bringing the two other queries together seem to solve the problem(if that makes any sense). Thanks anyway.

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

Similar Threads

  1. Dlookup
    By cbrsix in forum Forms
    Replies: 6
    Last Post: 11-01-2011, 10:38 AM
  2. DlookUp
    By cap.zadi in forum Forms
    Replies: 5
    Last Post: 09-21-2011, 07:16 PM
  3. Dlookup
    By pcandeias0 in forum Programming
    Replies: 3
    Last Post: 07-09-2011, 02:31 PM
  4. Need help with dlookup.
    By Keith in forum Database Design
    Replies: 8
    Last Post: 05-24-2010, 06:28 PM
  5. Dlookup??
    By Vikki in forum Access
    Replies: 4
    Last Post: 02-16-2010, 07:59 AM

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