Results 1 to 4 of 4
  1. #1
    caper is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8

    Access Dlookup of a Dlookup Assistance

    Hello! im stumbled on this...and i think its a lack of Access fundamental training....i've made the explanation as simple as possible.



    I have a "Table1" that list "Name" & "Job_Number". I have a form that does a Dlookup against the "Job_Number" and list on another text cell the "Name" associated via that Table1. This is no big deal.

    However, I then want the form to also display in another Textbox all the email's that are associated with that "Name" the dlookup solved for. So for instance, if the Dlookup solved that manually entered Job_Number: 11001 was Bob's job, then it displays BOB Y is populated in the txtPM text box. Then I want Bob Y's email, his boss's and the sales guy's emails to populate in the field (bob@live.com;boss@live.com;sales@live.com). I originally had that setup in another "TABLE2" like the below for ANOTHER dlookup, but for some reason, i cannot DLookup while usnig the Name's dlookup's value...I dont care if its on a query, form, or table...i just need it to list all the emails so that I can VBA it back into the Auto Emailer I set up. So right now, it displays BOB like it should, but the email text box, "#Error"

    thanks!!!!

    Table 1:
    JOB_Numbers____ Name
    11001 _________ _ Bob Y
    11002 _________ _ Dob D
    11003 _________ _ Cob S

    Table 2:
    Name2 Email2
    Bob _____ bob@live.com;boss@live.com;sales@live.com
    Dob_____ dob@live.com;boss2@live.com;sales2@live.com
    Cob _____ cob@live.com;boss3@live.com;sales3@live.com

    Project Number: Manual Entry "txtjobnumber"
    Project Manager: Dlookup below "txtPM"

    Working Code
    1.) gets the Name from the manually entered Job:
    =DLookUp("Name","Table1","Job_number=" & [txtjobnumber])

    2.) Does not work =DLookUp("Email2","Table2","Name2=" & [txtPM])

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,609
    Because the name is a string you need to enclose it in single quotes:
    =DLookUp("Email2","Table2","Name2='" & [txtPM] & "'")
    Cheers,

  3. #3
    caper is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    IT WORKS!!! ok now that it does....can you explain the logic in more detail...thanks!!

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,609
    The master explains it much better:

    http://allenbrowne.com/casu-17.html

    Basically for text (string) you need to wrap it in single quotes (or safer in double double quotes "" if you might get an apostrophe in the string such as in O'Hara), for dates you wrap them in # and nothing for numbers (like you had it- that is why your job number lookup worked).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Dlookup Assistance
    By Thompyt in forum Queries
    Replies: 4
    Last Post: 06-11-2017, 06:20 PM
  2. Dlookup Assistance
    By Thompyt in forum Programming
    Replies: 3
    Last Post: 09-22-2016, 03:18 PM
  3. Shortening if / dlookup >0 / then dlookup
    By Jack Russel in forum Modules
    Replies: 1
    Last Post: 02-19-2016, 03:01 PM
  4. Replies: 15
    Last Post: 07-27-2014, 10:04 AM
  5. dlookup assistance
    By underscore in forum Access
    Replies: 4
    Last Post: 12-28-2011, 06:32 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