Use DLookup to Look Up a Value from a Table

In this video, I'll show you how to use the DLookup function to look up a value from a table or query. We will look up a sales rep's phone number based on his ID.

Mitchell from Akron, Ohio (a Gold Member) asks: Each of my customers have an assigned sales rep. I've got the rep's name displayed in a combo box on the customer form. Is there a way that I could have his phone number appear next to it so I don't have to keep clicking and loading different forms? I tried making the phone number the second column in the sales rep combo box, but people still have to click to open it up to see it, and sometimes people accidentally change the rep. I just want the phone number to appear next to his name in a text box.

Common Formats

• DLookup("Field", "Domain", "Criteria")
• DLookup("FirstName", "CustomerT", "CustomerID=5")
• DLookup("FirstName", "CustomerT", "CustomerID=" & CustomerCombo)
• DLookup("StateName", "StateT", "StateAbbreviation=""" & State & """")
• DLookup("OrderID", "OrderT", "OrderDate=#" & LastOrderDate & "#")
• DLookup("CustomerID", "CustomerT", "FirstName=""" & FN & """ AND LastName=""" & LN & """")

Members

Members will learn how to handle DLookup criteria involving text strings and dates and use NZ to deal with NULL values. We will learn about AND/OR conditions in criteria. We will see how to use DLookup with DMax and DSum to get the date and amount of a customer's most recent order.

•  Extended Cut Video - Silver
•  Gold Member Template Download - Gold

Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!

Links

• Relationships: https://599cd.com/Relationships
• Relational Combo Boxes: https://599cd.com/RelationalCombo
• NZ Function: https://599cd.com/nz
• Concatenation: https://599cd.com/Concatenation
• Double Double Quotes: https://599cd.com/DoubleDouble
• DMax Function: https://599cd.com/DMax
• Old DLookup Tip: https://599cd.com/DLookupTip
• DLookup in Access Expert 10: https://599cd.com/ACX10
• DLookupPlus in Access Developer 17: https://599cd.com/ACD17

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.

Keywords

microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, dlookup, dlookup function, dsum, dmax, return a value, look up a value, lookup value, criteria, what is dlookup, argument, getting a value from a table or query, dlookup multiple criteria, dlookup multiple values, dlookup criteria from another table, dlookup query, dlookup error, dlookup with string criteria, dlookup with date