How to use the DLookup Function to Look Up a Value from a Table or Query in Microsoft Access.
ฝัง
- เผยแพร่เมื่อ 16 พ.ค. 2021
- 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:
TH-cam: / @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.
You have been the foundational mentor for helping build a database for my department. Thank you so much for everything!
You're very welcome. :)
This is one of the best and clear lessons on access I’ve ever found
Wow, thanks!
Thanks Mr. Lost For this one to, I always feel Better when i watch your Videos
You're welcome. - Mr. Rost
thumbs up as always. thanks a lot Richard for another valuable tutorials
Richard you are my absolute "Go To"!!!! Thank you.
Rock on!
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.
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.
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
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.com/ask
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?
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).
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
It should update when you pick the new sales rep.
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
Don't use split forms. They're evil. 599cd.com/evil
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
I suppose it's possible, but that sounds messy.
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
Post your question in my Forum: 599cd.com/AF
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.com/ask - need more info
WHy do you have to store info in "CustomerT"? And what would you actually be storing?
You store customer info in a customer table.
how do i change the fuel levy on our data base invoices?
I have absolutely no idea what you're talking about sorry.
Am able to do Dlookup but the values are not being saved on table, what am I doing wrong
Is the control bound to a field in a table?
I am already a gold member applied through website now how can i see extended cut videos in youtube please inform
If you signed up on my website you can only watch extended cut videos there. I have no control over TH-cam memberships.
how will i save the dlook up values into a table?
With an event. 599cd.com/QQ
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.com/QQ
I need to create an accounting in ms access....can I join to tech help...thanks
Yes you can: 599cd.com/TH
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.com/Ask
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.com/Inventory
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 😄
Check this out: 599cd.com/VLookupInAccess
What I want to find Lastname and firstname where lastname is lastname and firstname is firstname.
This makes absolutely no sense whatsoever. I'm sorry. Can you please be more specific?
So DLOOKUP essentially saves you the trouble of creating a million little queries for one-off uses?
Pretty much, yeah.
If I know the value I need is in the combobox list column, I use Me.ComboBox1.Column(2)
yep