How to use the DLookup Function to Look Up a Value from a Table or Query in Microsoft Access.

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.
BONUS FOR MEMBERS:
Silver Members and up get access to an Extended Cut of this video. 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 to get the date and amount of a customer's most recent order.
MEMBERS VIDEO:
• DLookup Function - MEM...
BECOME A MEMBER:
KZbin: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
599cd.com/DLookup
LINKS:
Relationships: 599cd.com/Relationships
Relational Combo Boxes: 599cd.com/RelationalCombo
NZ Function: 599cd.com/nz
Concatenation: 599cd.com/Concatenation
Double Double Quotes: 599cd.com/DoubleDouble
DMax Function: 599cd.com/DMax
Old DLookup Tip: 599cd.com/DLookupTip
DLookup in Access Expert 10: 599cd.com/ACX10
DLookupPlus in Access Developer 17: 599cd.com/ACD17
COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Paid TechHelp Questions: 599cd.com/TechHelpPaid
Consulting Help: 599cd.com/Consulting
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
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
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@Iloveswedes]

You have been the foundational mentor for helping build a database for my department. Thank you so much for everything!

[@599CD]

You're very welcome. :)

[@patrickwachira]

This is one of the best and clear lessons on access I’ve ever found

[@599CD]

Wow, thanks!

[@Jojosmith342]

thumbs up as always. thanks a lot Richard for another valuable tutorials

[@musokeherbert8511]

Thanks Mr. Lost For this one to, I always feel Better when i watch your Videos

[@599CD]

You're welcome. - Mr. Rost

[@SavedbyGraceCT]

Richard you are my absolute "Go To"!!!! Thank you.

[@599CD]

Rock on!

[@philipmcdonnell7168]

Thanks for sharing, Richard. At work I am hamstrung by our IT Dept because it refuses to allow macros or VBA to be used out of virusphobia (that word doesn't exist...). Consequently, I can't take my db to the level I want to. If functions like DLookup can help me get more functionality, it will help. Live long and prosper.

[@599CD]

Yeah... there's nothing worse than overzealous IT guys... I hate that. My entire Expert series teaches you all kinds of cool stuff without macros or VBA. 599cd.com/Expert

[@cbell9100]

The problem I have with form, subform relationship each based on one table is when new entries are made and the user clicks back and forth between the two it will often give errors especially when it runs a validation module. I try to train (and make msgbox pop up telling them what to do) the users to go to form data first then go to subform data but the same form built on a query doesn't have that problem. I try requery on got focus or lost focus but that doesn't solve the issue. There's probably a simple solution to this problem but this is why I have built off queries instead of one table form/subform.

[@ssdusd]

I realy like this video it works with me. but I have small propelm, I put it in split form it works fine, but I could not filter if I have Unbound textbox

[@599CD]

Don't use split forms. They're evil. 599cd.com/evil

[@evihan1569]

Thank You for the Video. I have a question. Let's say you picked the wrong sale rep and want to choose someone else. I do you make the phone number appear without having to refresh the whole page. Thank you very much

[@599CD]

It should update when you pick the new sales rep.

[@christophernichol9079]

Just a quick query, can you do a dlookup based on several different fields which have been concatenated into one text box? Many thanks Chris

[@599CD]

I suppose it's possible, but that sounds messy.

[@kutub52]

Can we check repeat cell under same table of access like for e.g i want to put an extra column where looking at one entire field of one table access can show me repeat in that extra made colomn?

[@599CD]

599cd.com/ask

[@halabaloux]

I just got hired at a small ink and toner shop. On my first day they showed me a call list folder of customer we can call to see if they need any printer supplies. That desktop folder contained 30 different excel files dating back to 2016. (When the company started) I thought why isn’t that complied into a data base? And since I had beginner level knowledge of access, and found that the company had access on their computer I thought I’d create the needed database. This is when I found your videos to expand my knowledge of access. The point: I have my costumerT and I have an isActiveQ. I have my custF based on the isActiveQ because I only want to see the the active people that we sell to. In this video you say not to base forms on queries. Is there a way to tell the form to only show me the active customers if I base the form on the table instead?

[@599CD]

599cd.com/QQ

[@thejose970]

The current way I "autopopulate" customer details in my service forms is to make an invisible combo box with a query with the fields I want to fill, then use the on load event with the following code me!Field.Value = me!Combo.Column(0) me!Field2.Value = me!Combo.Column(1) And so on I did it this way since I didn't know Dlookup was a thing, should I change it?

[@599CD]

Well, I've always been of the mindset "if it ain't broke, don't fix it," but you may find DLookup will improve your performance. PLUS, you can't use combo box columns for things like Long Text fields (memos).

[@NoOne-wm8ui]

Hi i have been watching your tutorial, How can i use a relational combo box to filter data that is a lookup field of two tables?

[@599CD]

599cd.com/ask - need more info

[@mominadil2279]

Hello sir, thanks for the content you share, they are very helpful. A small question please, I'm trying to create a custom report where I get from queries only totals (sum) into a single field and not a list. I'm trying to view after cross tab VAT and Month to view Gross_SUM per month and VAT percentage. I get the results successfully, although I try to extract a specific value sum from the table, but it won't happen, and I don't know why. I mean, I want to view 19% VAT from the different VATs corresponding to a month like January. They are shown in the cross tab query, but I don't know how to extract that specific value from the query. I hope someone can help me. Your help is extremely appreciated

[@599CD]

Post your question in my Forum: 599cd.com/AF

[@DavidWilliams-wj4sc]

WHy do you have to store info in "CustomerT"? And what would you actually be storing?

[@599CD]

You store customer info in a customer table.

[@Djisback02]

I am already a gold member applied through website now how can i see extended cut videos in youtube please inform

[@599CD]

If you signed up on my website you can only watch extended cut videos there. I have no control over KZbin memberships.

[@user-dd9fk1ys9p]

I want to enter the monthly performance of the personnel in Access every month, of course, by uploading the Excel file, but I want the performance of each month to be in exactly one column and by using the personnel code, the performance of each person should be placed in front of him. Please use the combo box to select the month of performance. Thankful

[@599CD]

599cd.com/Ask

[@adamjellinek9141]

Po pierwsze dziękuję za dużą bazę wiedzy, z której można korzystać i Twój wkład w naukę Access. Niestety nie mogę nigdzie znaleźć odpowiedzi na moje pytanie, więc może Ty pomożesz. Na przykład dziewczyny po ślubie zmieniają nazwiska, w związku z tym mam osobną tabelę z nazwiskami pracowników i datą zmiany. Jak stworzyć kwerendę która dla danej daty dobierze sobie pasujące na ten dzień nazwisko. W Excel jest funkcja wyszukująca z dopasowaniem przybliżonym, a Access uparcie podaje mi zwielokrotnione wyniki z każdym nazwiskiem. Da się to zrobić, a może muszę przeprojektować bazę? Bardzo proszę o pomoc. Tego typu historyczne dane, są z punktu widzenia HR istotne i w mojej pracy bardzo potrzebne. Pozdrowienia z Polski 😄

[@599CD]

Check this out: 599cd.com/VLookupInAccess

[@jodiemcleod260]

how do i change the fuel levy on our data base invoices?

[@599CD]

I have absolutely no idea what you're talking about sorry.

[@patrickwachira]

Am able to do Dlookup but the values are not being saved on table, what am I doing wrong

[@599CD]

Is the control bound to a field in a table?

[@mosjany]

how will i save the dlook up values into a table?

[@599CD]

With an event. 599cd.com/QQ

[@syedikramullah1114]

How to make inventory management in access in which sample adds, in stock&issue from stock only this type of column available. No purchase and sale in it. How to make and which formulas will be used to make like this type of inventory software.

[@599CD]

599cd.com/Inventory

[@liemkhen2668]

I need to create an accounting in ms access....can I join to tech help...thanks

[@599CD]

Yes you can: 599cd.com/TH

[@tutsecret499]

What I want to find Lastname and firstname where lastname is lastname and firstname is firstname.

[@599CD]

This makes absolutely no sense whatsoever. I'm sorry. Can you please be more specific?

[@artistryartistry7239]

So DLOOKUP essentially saves you the trouble of creating a million little queries for one-off uses?

[@599CD]

Pretty much, yeah.

[@MARC1TIM]

If I know the value I need is in the combobox list column, I use Me.ComboBox1.Column(2)

[@599CD]

yep