Results 1 to 13 of 13
  1. #1
    KarenElissa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    7

    Dlookup help


    I'm trying to use dlookup in a report. I think the first half is going fine, I have =DLookUp("[Grades]","[Report_Card_Grades]") and that pulls the first option from the "Report_Card_Grades" table, but I need it to pull the actual text that matches the number that is currently showing up in the field. That is in the second half, but I haven't had any trouble getting it to work. So help please!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    KarenElissa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    7
    Ya, I've looked at that (or something very like it), but I couldn't get it to work. This seems to be the version I need?

    For numerical values:
    DLookup("FieldName", "TableName", "Criteria = " & forms!FormName!ControlName)

    I think I'm stuck on the criteria part? The very last part is just the name of the field I'm working on in the form I'm doing, right?

    Thanks!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The word Criteria would be replaced by the name of the field in the table you want the filter applied to.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    KarenElissa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    7
    Still not getting it right. I have:

    =DLookUp("[Grades]","[Report_Card_Grades]","[Report_Card_ID]=" & [txt_Understanding_1])

    where [Grades] is the field in [Report_Card_Grades] that has the text I want to see on the report. [Report_Card_ID] is the ID# for the [Grades]. And [txt_Understanding_1] is the name of the text box in the report where I want words to show up.

    And all I'm getting is #Type!

    Thanks!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the data type of Report_Card_ID? What is contained in the textbox? Or can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by KarenElissa View Post
    Still not getting it right. I have:

    =DLookUp("[Grades]","[Report_Card_Grades]","[Report_Card_ID]=" & [txt_Understanding_1])

    where [Grades] is the field in [Report_Card_Grades] that has the text I want to see on the report. [Report_Card_ID] is the ID# for the [Grades]. And [txt_Understanding_1] is the name of the text box in the report where I want words to show up.

    And all I'm getting is #Type!

    Thanks!
    PMFJI.
    Replace txt_understanding_1 with the name of the form control where your Report_Card_ID value is displayed
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    You want the results of the DLookup to appear in txt_understanding_1?

    txt_understanding_1 = DLookup("[Grades]","[Report_Card_Grades]","[Report_Card_ID = " & [whatever field holds the ID in your report recordsource])
    Last edited by davegri; 10-01-2018 at 02:22 PM. Reason: clarif

  10. #10
    KarenElissa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    7
    That was my problem, I still need to have a box with the number in it to get the text in the other box, I was skipping that part. Ok, one more question, if there is nothing in first box, I get an #Error, how do I make it just empty? Thanks!

  11. #11
    KarenElissa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    7
    I'm trying to get rid of the #Error if there isn't any info, I've tried both:

    =DLookUp("[Grades]","[Report_Card_Grades]","[Report_Card_ID]=" & Nz([txt_Assignments_1a],"---"))

    and

    =Nz(DLookUp("[Grades]","[Report_Card_Grades]","[Report_Card_ID]=" & [txt_Assignments_1a]),"---")

    and I'm still getting errors with both.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Try Nz in both locations.

    =Nz(DLookUp("[Grades]","[Report_Card_Grades]","[Report_Card_ID]=" & Nz([txt_Assignments_1a],0)), "---")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    KarenElissa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2018
    Posts
    7
    That's it! Thanks everyone!

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

Similar Threads

  1. Another Dlookup ?
    By Thompyt in forum Programming
    Replies: 1
    Last Post: 01-04-2018, 02:15 PM
  2. Shortening if / dlookup >0 / then dlookup
    By Jack Russel in forum Modules
    Replies: 1
    Last Post: 02-19-2016, 03:01 PM
  3. Like in Dlookup
    By msp4422 in forum Programming
    Replies: 3
    Last Post: 02-19-2013, 04:02 PM
  4. DLookUp Help
    By ThaGreenMoose in forum Forms
    Replies: 10
    Last Post: 06-15-2011, 12:08 PM
  5. DLookup help
    By denners05 in forum Access
    Replies: 1
    Last Post: 06-11-2011, 12:55 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