DLookup Tricks
By Richard Rost
12 months ago
Microsoft Access DLookup Tips You May Not Know In this Microsoft Access tutorial, we will explore three advanced DLookup tricks to enhance your database development skills. Learn how to return multiple values with a single DLookup call, perform basic arithmetic operations directly within DLookup, and create compound statements using DLookup in combination with other functions like DMax. These tips will help you write more efficient code, and I'll even show you how to integrate complex DLookup statements directly into form fields for streamlined data retrieval. Whether you're working with VBA or within forms, these techniques will simplify your coding process and improve database functionality. MembersThere is no extended cut, but here is the file download: 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, DLookup tricks, DMax examples, VBA DLookup, concatenation in VBA, multiple values DLookup, DLookup string manipulation, compound statements in DLookup, DLookup with math operations, DMax within DLookup, DLookup in forms, optimize DLookup, control source DLookup, VBA variable declaration, debugging VBA code, network performance tips, SQL alternatives for DLookup
Subscribe to DLookup Tricks
Get notifications when this page is updated
Intro In this video, we will talk about three advanced DLookup tricks in Microsoft Access using VBA. You'll see how to return multiple values from a single DLookup call, perform basic math operations within DLookup, and create compound expressions by combining DMax and DLookup together. We'll also look at how to use these techniques in a form's control source and discuss when it is best to avoid DLookup in continuous forms to prevent performance issues.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor Richard Rost. Today, we're going to go over three DLookup tricks you might not have known about.
Here we go. I am going to consider this both an expert and developer level lesson. I'm going to use VBA, but the same stuff can work in a query or in a form. In fact, I'll show you an example with a form toward the end, but I'm going to use VBA. It's not that it's the same thing. It's the same DLookup code. But before we get started, if you've never used DLookup before, go watch this and learn DLookup before I show you the DLookup tricks. We're also going to use DMax in one of the examples so go watch DMax too; it's DLookup's close cousin. Go watch my concatenation video. You should know how to concatenate two fields together. And if you're curious and you want to learn some VBA, go watch my intro to VBA video; it's about 20 minutes long and it teaches you everything you need to know to get started. These are all free videos. They're on my website. They're on my YouTube channel. Go watch those and then come on back.
All right, so here I am in my TechHelp free template. This is a free database you can grab off my website if you want to, and let's just do a basic DLookup example. I'll just put it in my hello world button, right click build event that'll bring up a code builder slide you down here. I've got status hello world; we'll just put hello world in the status box. I've got a whole separate video on how the status box works. I'll put a link down below if you're not familiar with it, but in here we can declare a variable dim s as a string. I'm going to say s equals DLookup. Let's just look up someone's first name from the customer table where customer ID equals one. I know that I'm number one and then we'll DLookup and then we'll status that s. Just look it up and then status it real simple. We know how to do this; save it, debug compile, and then we'll come out here and we'll do a little like and there you go. There's Richard.
Now, that's not one of the tricks. That's just a basic DLookup. Before I taught you, well, if you want to get first name and last name, it involves two DLookups. You look up first name, then you look up last name, then you concatenate them together and do a new string right. Well, trick number one is you can actually return multiple values with one DLookup call. It's actually faster too. Instead of 10 DLookups, put it on one if you can. So let's say you want first name and last name, let's say last name comma first name. Okay, here's what it's going to look like. It's going to be last name. Now, don't just put a comma in here. You have to actually treat this like a string. So it's going to be last name and double double quotes comma double double quotes as a space in there and then and first name so it's going to take first name. It's going to put quotes comma space and then quotes in there. That's what you want to actually have displayed and then first name.
If you don't know what double double quotes are again, I've got a whole separate video on that. It's part of concatenation. I'll put a link down below. But now when I do this, look at that, it returns both fields at once. Bet you didn't know you could do that. You can return multiple fields in a single DLookup statement. Want to add customer ID in there? Let's add customer ID and then a colon so it says one colon Rost comma Richard so put customer ID right and then and double quotes colon space double quotes and last thing just like that. See this becomes a literal string inside the lookup. So it's only looking up this. It's looking up this. It's looking up this from the same DLookup call, which is faster than three and then it adds this stuff in as part of the string ready? All right, you got debug compile make sure it's good and then oh look at that one colon Rostcomma Richard so if you got multiple DLookups you can get away with that all right. That's trick one. That's pretty cool.
All right trick number two, I'm just going to rem this out so the syntax is in there for the gold members when you download the database. All right number two, you can do basic math inside a here too. So for example, I'm just going to copy and paste here. You can say this you can say s equals DLookup price times quantity. Right now I don't have all of this setup. So let's just DLookup something stupid. Let's do in my customer table I've got a numeric field over here. I've got family size. Let's just double the family size. Okay, so you could do basic math in here. So I could say s equals DLookup family size times two from my customer T again for me. So the customer ID equals one or you could do price times quantity or any kind of math that you want. Ready click there's a four. Same that's pretty neat too.
All right trick number three is a compound statement. You can put DLookups and DMaxes and all kinds of other stuff inside of DLookup functions. You can use them as results for each other instead of having to do three different functions. Say, for example, you want to get the name of the last customer to place an order. Okay, so that would involve a couple of different lookups. So you have to go to the order table. You have to find out who placed the last order. Let me make it me. I'm going to make it me. So I'm customer one. I'll make this the last one here. So now if I sort this I should come up as the last customer to place an order. Oops, I want to be me again. It was auto returning from a security check on before anyways. So to get this information I would first have to do a DMax to get the order date. Right the max of the order dates. Then I had to do a DLookup in the order table to get customer one. Then I'd have to take that customer one and do a DLookup in the customer table to get first name and last name. So that's three separate actions. Now we could do those as three separate calls and in fact sometimes it's easier to write them all out that way. But you can do it all in one call for example s equals.
All right, we're going to start with let's let's start with the outside one first. So we want the last name and first name so in fact I can just copy this guy up here. Can I will just do the same thing as this. All right, but we don't know the customer ID. So let's get rid of customer ID. We want last name and first name. Okay. I'm missing something here. Let's see. Gotta have a quote in front of that. This still has to be encased inside of quotes. Okay, so I'm looking up the last name and first name from the customer T where the customer ID equals something. All right now to get this I want the customer ID from the order table where the order ID or excuse me where the or the map the order date is the max. Right so we're going to replace that one within x. All right now. What's that x going to be? I'm just going to come down here and write that x. Okay, that's going to be and I'm doing I'm gonna put an x here, but we're not gonna use it. I'm just gonna put the command here. It's gonna be DLookup customer ID from the order table where the order date equals some date why right and then we'll actually forget to put quotes around this too this and then this right because we have to put the date inside of these guys. Okay, and now I need some date why well what's why gonna be? Well why is the max from order the order date from the order table Okay.
Now normally we'd do this in reverse order we'd look up the DMax first then we'd look up the customer ID from the order table that matches that order date then we'd look up their last name and first name, but we can do this all in one step. Okay, so first we'll substitute why we'll take this DMax and we'll stick it right there. See so this DMax will run and it will fill in that order date equals whatever now this whole thing becomes that X and I'm gonna put the X I'm gonna put this outside of the quotes like this and that's one big giant long thing. Let's redo it a little bit with some line continuations like this and then like this. Okay, let me get rid of this. Okay, so see how it's working. It's gonna DMax the order date from the order table. That will become the order date we're looking up the customer ID from now we have the customer ID we can look up the last name and first name. Make sense? All right, save it and now click and there's Richard Rost and it happened all in one command. Yes, it is three separate lookups, but it's all one command. If this made sense to you feel free to use it. It's a lot easier to read the other way though. I'll be honest, but the other way requires some more variables too. Normally the way you do it is you'd say dim D as a date right and then ID as a long and then we already have S is a string so we can first say D equals DMax the order date from the order table. Okay, that's the maximum order date from the order table now we look up what customer that is so the ID equals DLookup customer ID from the order T that matches that date right or order date equals D. Okay, now we can get the first name and last name which is this. Right and this will put that into S and then this customer ID equals right here would simply be and ID. Those three commands instead of one you'll get the same result but if you like right now this is actually harder to write and harder to read so I don't know the benefit of it but if you think this way then feel free to use it if you like confusing other people and future you then use this. Why am I showing you this? I don't know. This is a kind of an extreme example. Normally I will use it for something simple. Like I'll use it just to DLookup the customer ID from the max order date. I'll do I've done this before many times. I just added this third layer on it to prove the point that you can do it I guess, but I'll often do a DLookup and then throw a DMax or even another DLookup inside of it okay.
And yes for for non-VBA purposes, let's put this back to one long string like we had it before. Okay, all right, that's one long command now now you can take this whole thing and put this in a form field if you want. I guess I guess this is where it's a benefit because you can take this big long complicated function now and stick it in the control source of a field without having to do multiple lookup fields. That's a pain right we got to do hidden you got to make hidden fields in your form. Remember the equal sign has to be right up next to it to use it in the control. So we're gonna select and copy that whole thing and then we'll come back to our main menu design view and we'll change this to last order placed by and then this is now going to be last order we're gonna get rid of the control source and put our function in there paste and then get rid of the format short date. And now we've got that big long function in here right and so now if I close it save it open it boom there it is. But I guess there is a benefit I wouldn't I wouldn't write this necessarily in VBA like I got it here but it is handy if you want to use it in a form and remember try not to use DLookup functions like this in continuous forms or in queries because it's got to run once for each row and it will slow things down especially if you're pulling data over a network. Try to keep this to single forms menu type forms with the data in them right the forms like this where you want to look something up.
Okay, if you want to use it in a continuous form or you want to do it in a query try to do it with SQL. That's a lot faster especially over a network. So there you go. There's some DLookup tricks if you have a cool DLookup trick or any of the D functions DMax, DMin. I've got videos for all of the different functions, but I'm always looking for tips and tricks if you can come up with something that's awesome. Post it in the comments down below if I haven't seen it before I'll make a video out of it. I'm out of ideas. These are all the DLookup tricks I know. I'm pretty basic I'm pretty meat and potatoes when it comes to DLookup. But there you go that's your TechHelp video for today. Hope you learn something. Live long and prosper my friends. I'll see you next time.
TOPICS:
Three DLookup tricks
Using DLookup in VBA
Returning multiple values with one DLookup
Performing basic math in DLookup
Compound statements with DLookup and DMax
Using DLookup in a form's control source
Considerations for using DLookup in continuous forms
COMMERCIAL:
In today's video, we're exploring three advanced DLookup tricks in Access VBA that could change the way you handle data. For those familiar with DLookup, you're about to discover how to return multiple values with a single call, perform basic arithmetic operations within DLookup, and even combine DMax with DLookup for compound statements—all within the same function. These techniques not only simplify your code but also improve performance by reducing the number of calls needed. If you're curious about how to integrate this into your forms without slowing down performance, you'll find these insights invaluable. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is one of the main benefits of using a single DLookup call instead of multiple DLookup calls?
A. It reduces the overall script size.
B. It is faster than making multiple separate calls.
C. It allows for data validation within the DLookup call.
D. It simplifies query design.
Q2. What is one of the tricks mentioned for the DLookup function?
A. Using DLookup to directly alter table data.
B. Integrating DLookup with external databases.
C. Performing basic math operations within a DLookup call.
D. Triggering DLookup through user input.
Q3. In the context of the video, how can multiple fields be returned in a single DLookup call?
A. By using multiple DLookups within a single statement.
B. By concatenating fields with double quotes and separators like commas or spaces.
C. By creating a subquery inside the DLookup call.
D. By writing a custom VBA function to handle multiple fields.
Q4. What additional D function is prominently mentioned alongside DLookup in the video, which is often used to find the largest value?
A. DCount
B. DAvg
C. DSum
D. DMax
Q5. What should you avoid when using DLookup in forms, according to the video?
A. Using DLookup in SQL statements.
B. Using DLookup for creating new fields.
C. Using DLookup in continuous forms or within queries to avoid performance issues.
D. Using DLookup for date fields.
Q6. What is one reason to integrate a DMax call within a DLookup statement as discussed in the video?
A. To filter out unwanted records from being looked up.
B. To find the most recent entry in a related table as part of a compound lookup.
C. To simplify error handling in VBA scripts.
D. To integrate with third-party systems for advanced lookups.
Q7. How does the instructor suggest optimizing performance when using DLookup in a networked environment?
A. By using stored procedures instead of DLookup.
B. By using DLookup only in local databases.
C. By integrating DLookup with global variables.
D. By using SQL for queries instead of DLookup in networked environments.
Q8. What is a practical application of using DLookup in a form field as described in the video?
A. To initiate form printing automatically.
B. To calculate the total number of records.
C. To dynamically display data like "last order placed by" using a single DLookup expression.
D. To create a duplicate record finder.
Answers: 1-B; 2-C; 3-B; 4-D; 5-C; 6-B; 7-D; 8-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone brings you a session on three lesser-known tricks for using DLookup in Microsoft Access. I'm your instructor, Richard Rost, and I'm targeting this lesson towards both expert users and developers. While I'll be working with VBA, rest assured that these tricks apply equally well to queries and forms. I will provide a form example at the end, but our primary focus is using VBA.
If you're not familiar with DLookup, I recommend watching my introductory video on the subject first. Additionally, one of our examples will utilize DMax, so a refresher on that would be beneficial. You'll also want to understand how to concatenate fields, so consider checking out my video on concatenation. Finally, for those curious about VBA, there's a starter video available on my website and YouTube channel that gives you the basics in about 20 minutes—all for free.
To get started, we're using a free template database from my site. We'll conduct a straightforward DLookup in which I'll assign a command to a button to show how to use DLookup for retrieving a first name from the Customer table. We then format and display it with a Status command. This basic demonstration familiarizes you with DLookup before we move on to the tricks.
The first trick involves returning multiple fields in a single DLookup call, improving efficiency. If you usually perform separate lookups for first and last names, you can now combine them. By treating the return statement as a string, you can format the result with spaces and commas, returning, for example, "Rost, Richard" in a single call.
The second trick is performing basic math within the DLookup. For example, multiplying a "family size" field by two demonstrates that you can conduct arithmetic operations directly within the DLookup call. This versatility allows you to retrieve and manipulate data simultaneously.
Our third trick involves using compound statements. Here, DLookups and DMaxes interact within each other. For example, you can determine the name of the last customer to place an order by combining multiple functions. While normally you would execute these lookups in a sequence, a more complex, single expression can achieve the same result. Though more challenging to write and read, this method encapsulates several operations in one formula, useful in specific scenarios like form controls where efficiency is paramount.
In summary, while the single-command option can be complex, it is useful when applied to form control sources, relieving you from creating multiple fields for each lookup. However, be mindful of its limitations in continuous forms and queries as it might affect performance. For those applications, prefer SQL due to its speed and network efficiency.
If you have your own DLookup tips or tricks to share, please leave a comment below. I'm always eager to discover new methods and potentially create a video if the technique is novel. I hope today's TechHelp tutorial has been insightful.
Remember, you can find the complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Three DLookup tricks
Using DLookup in VBA
Returning multiple values with one DLookup
Performing basic math in DLookup
Compound statements with DLookup and DMax
Using DLookup in a form's control source
Considerations for using DLookup in continuous forms |
