Results 1 to 4 of 4

Thread: DLookup

  1. #1
    Join Date
    Jun 2011
    Location
    Orlando, FL
    Posts
    6

    Unanswered: DLookup

    What's wrong here? I keep getting #Error ???

    =DLookUp("[Status Name]","Status","[StatusID] = " & [StatusID])

    DB has two tables = CONTACTS & STATUS - the Contacts table uses a drop down of the Status table and enters a 3 digit numeric code in the StatusID fields. Both fields are set to format as text even though the codes are numeric. The STATUS table has only 2 fields: StatusID & Status Name.

    I have a text box in a report using the above string to attempt to show the code and the name assigned to that code from the STATUS table on the report. I dropped the 3rd segment for criteria of the string and then it returns one of my status names, but not the correct one?

  2. #2
    Join Date
    Jun 2011
    Location
    Orlando, FL
    Posts
    6

    more info ...

    The report is based on a query of CONTACTS that has all of the same fields from the table CONTACTS. The query has 2 variable fields you're prompted for as you click on it ... "District" & "Status" - so the query should be pulling on 1 status at a time for the report.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Assuming that all of the names are correct, in Table Design, is [StatusID] defined as a Number Datatype? The syntax you're using is correct for a Number in the Criteria, but if it's defined as Text,

    "[StatusID] = " & [StatusID]

    should be

    "[StatusID] = '" & [StatusID] & "'"

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jun 2011
    Location
    Orlando, FL
    Posts
    6

    2nd attempt

    All fields are set to TEXT.

    Here's the new string:

    =DLookUp("[Status Name]","Status","[StatusID] = ' " & [StatusID] & " ' ")

    This stopped the #Error from being returned, but the report is not just blank where the status name text box should be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •