DLookup Multiple Criteria 2
By Richard Rost
2 years ago
DLookup with Multiple Criteria in Access, Part 2 In this Microsoft Access tutorial, I will show you how to use the DLookup function with multiple criteria, focusing on string handling, criteria building, and error management. This is part 2. MembersThere is no extended cut, but here is the database 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 multiple criteria, DLOOKUP example, DLOOKUP tutorial, Access DLOOKUP, Boolean logic in DLOOKUP, Microsoft Access criteria strings, Access calculated fields, advanced DLOOKUP, concatenation in Access functions, preventing null errors in Access, NZ function in Access, Access string manipulation
Intro In this video, we continue working with Microsoft Access and explore how to use the DLOOKUP function with multiple criteria, focusing on building complex criteria strings with Boolean logic for fields like quantity and tier. I will show you how to properly handle string values, use concatenation to substitute field values into your criteria, and ensure your DLOOKUP statements work correctly in calculated fields. We will also cover using the NZ function to manage null values and avoid errors in your queries. This is part 2.Transcript Today is part 2 of my DLookUp multiple criteria series, and I don't know how many parts there are going to be. This is part 2. I never know how many parts there are going to be until I'm done. Sometimes I take topics and I expound upon them. Sometimes stuff goes long. I don't know. So we'll just figure it out. If you haven't watched part one yet, go watch that first and then come on back.
In part one, we learned about Elliot's little grid here. We take the quantity, and you have to find the quantity between these two values in that pricing tier, and then you can return a discount. In the last video, we got all that set up. We got our discount table right there, and we got our order table where we put in the number of units that the person purchased and the tier, and then we're going to look them up.
But let's take a look at the DLookUp itself. Let's start off by just addressing what we are trying to do, and then we'll slowly convert that over into a criteria string for our DLookUp.
We've got 12 units in tier A. We're basically saying, "Okay, 12 has to be greater than or equal to the minimum quantity, and 12 has to be less than or equal to the maximum quantity, and the tier has to be A." So how would we write that as a criteria using the kind of Boolean logic that we see in our criteria strings?
First off, 12 - our value, our units - is greater than or equal to the minimum quantity. Then we have to add on an AND condition. AND 12 is less than or equal to the max quantity. And then we also have to add on the tier. The tier equals A.
You can write it the other way if you want to. If you're happy with that, you could say min quantity is greater than or equal to 12. That doesn't matter; it's the same thing. Do you need parentheses around all this? No. You only need parentheses if it's one or the other, or you've got an OR condition in there, and something else has to be in there. Don't worry about the parentheses for now. We don't need parentheses for this example.
The next thing to consider is that tier is a string value. It's not a number. Remember, string values have to be enclosed inside double quotes. Make sure you put your double quotes on it. If you have a date value, they've got to be enclosed inside pound signs. So if we did have a date, we'd have to put it inside pound signs. But we don't, so we don't need that. Don't worry about that.
So that's where we're at. The next thing to remember is this whole thing has to be inside a string value. Here's a complete DLOOKUP statement that you should be familiar with. If I'm looking up the first name from the customer table, here's the criteria right there. It's right here. This is the selected text from here to there. That's all inside a string.
So, our criteria that we're building has to be inside quotes itself. This whole thing is a string value. What does that mean? We're going to put a quote here and a quote out here. But if you've got quotes inside quotes, you have to replace each of these with double double quotes. This one has to be double double quotes. I'm going to bold these ones and make them red, just so they look different. We're going to make these guys red. These are the quotes around the A.
Can you use single quotes? Yes, in this example you could, but I don't like single quotes. I have a whole separate video on why. Here's a video about it if you want to learn more. I just say use double double quotes in Access. Stay away from single quotes, my opinion. I think it would be a whole lot easier in Access if Access would colorize stuff like this too. So Sammy, put that on the list. That'll never get added, but put it on the list anyway.
For those of you who don't know, we're keeping a wish list of things we'd like to see added to Access. If I ever do get in front of the Access team to talk to them about it, it's about 150 items long now. It's getting up there.
The next thing we're going to do is substitute our actual values. 12 is just something we put up here to make this make sense. That's actually the field called Units. Now we're going to take this 12 and replace it with Units, but we're going to replace it outside the string. See what I did there? I put Units outside the string, and then with a little concatenation, we add it in. This will get replaced with units.
Let me see if I can do this. There we go. That units will be replaced with a 12 when this string actually gets figured out, when Access compiles the string. So it'll be units, which is 12, goes there. Now we'll do the same thing here and replace that with units. But again, it's going to be outside the string. So it's going to be quote and units and quote, like that. This takes units and brings it outside the string.
Now we also have to do the same thing with the A from the tier. This has to be brought outside. I'm going to just make this two lines and make it easier. This is the same line, same command. It is all inside the same string. Now this is where this gets trickier because we're going to cut that A out and put the word tier in there. But again, we have to close the quotes, add tier, and then open the quotes again. This is the double quote inside the string. This closes the string. Then we add tier. Then we have to open the string up again, put double quotes inside the string, and then close the string itself one more time.
I think now we're ready to take this criteria over to Access and put that in an actual DLOOKUP statement. Let's go back over to our database. I'm going to put this in a query. Let's create query design and just to keep things simple for now, I'm just going to bring in the order table. I just want to see the order ID, the order date, and the units and the tier are the two things that matter. Over here, we'll put a calculated field in here with our discount.
I'm going to zoom in, shift F2. We're going to call this discount. That's the calculated field value. It's going to be DLOOKUP. What are we looking up? Discount from what table? The discount T. Here is where we put our criteria that we just worked so hard on. I'm going to go back over to PowerPoint. Let me put this in one line so it'll copy nicely. I'm just going to copy this and come back over here and paste it in just like that. Then I'll put a close parenthesis on the end of it, and that should be our completed function. Let's run it and see what we get. Look at that. Perfect. We'll deal with the errors and the stuff in a minute.
Let's verify our values. Give me my discount table. What do we have? 12 and A should be 0.1. That's 10. So 12 falls between these, and that's A, and that's a 10. Good. We can format this later. 18 in A. So 18 should fall in this tier. Perfect, 15. 6B is right there, and 11C would be right there, 19. Perfect.
Let's put one more in down here. 35C. And I got a 22. Excellent. So, our formula is working so far. Now, let's handle this error. If the values aren't found in the table, it's going to return an error. We can get rid of that error with the NZ function. I should have put this on your prerequisites. I'm sorry, but go watch it now if you've never used NZ before. NZ basically says, if you're going to return a null value that's going to give me an error message, I don't want a null value. I want you to convert it to zero. Or for strings, you can make it an empty string. Or for dates, you can make it some really old value, like 1990. It's basically saying that if the value doesn't exist and you're going to get a null, give me something else.
There's a couple of places we've got to put NZ. Let's start with one of them. Come in here, Design View. Let's go back to our big function. Now, we can wrap this whole thing in NZ like that, and then put a comma zero on the end, which means if this whole thing is going to return a null value, give me a zero. But if I run it, I still get errors. Why? That's because the values we're sending into it are also zeros. We have to take those into consideration too. Where we have units and the tier, we have to use NZ as well. We can say, if units are null, send it a zero. It can't look up null on the table, that's why we're getting an error message. It will just never return a value, so this will return a null on the outside; NZ will take care of that. We also have to escape Units, comma, zero and do the same with the tier and return an empty string since this is a string value. Save this. I'm going to call this my order discount queue. Now when we run it, no more errors, and we get our discount as always zero.
That's your big nasty DLOOKUP function. You can't practice this enough, folks. This was just as confusing to me when I first started doing this stuff. I know a lot of you, your heads are spinning. Mine was too. It took me a lot of practice to get this stuff down to the point where I could teach it. You know how they always say that when you teach people stuff, you learn it better yourself? When I started teaching this stuff in the classroom, like late 1990s, 97-ish, and I had to prepare lessons on it, it helped me to better understand it myself. So, if you can explain this to someone else, that will actually help you learn it yourself. Your brain kind of rewires it differently when you teach something. I've taught this, I don't know how many times, how many hundreds of videos.
We'll do more of these as time goes on because I get asked a lot of this stuff, especially like with multiple dates and stuff. If you've got a good DLOOKUP question, then send it to me. In fact, here's one that was just posted a couple of hours ago in the forum by one of my students, Andrew. One of the difficult things for me, as both a consultant and as a teacher, is to look at your field names and try to understand what's going on. What is AQL? I don't understand what his terms are, but it's very similar to what we just went over. He's got a table where he's got a start value and an end value. This AQL looks like his tier that I was using A, B, and C for. He wants to be able to return this number here. Someone puts in a lot quantity, basically the number of units they purchased. It has to fall in this range. Pick the AQL, whatever that means, 6.5, 2.5, whatever. So it's going to match that, and he wants to return this value.
That's almost exactly what we just did in this video. Here's the function that I helped him with. Look up the ID in that table where the lot range start is less than or equal to the lot quantity and greater than the other quantity, and it's got to be equal to whatever that AQL value is. Those are all numbers, so that's even easier.
Now, you've got this discount figured out. How do you incorporate this discount into your order system? That's the question. If you look at my order entry system, let's say I'm going to give these order discounts to that order. Apply it to that total there. How do you do that? If you want to see how to do that, post a comment down below. If enough of you are interested, we'll make a part three and I'll show you how to incorporate this discount into this order form total here.
But for today, that is going to be your TechHelp video. I hope you learned something. DLOOKUP is one of those very, very powerful functions that I could do dozens of lessons on. I cover it in a lot more detail in my expert series. In fact, I just went to my course outline index and I covered it. There are 68 entries for it. It's in Expert 10, Expert 11, it's in a bunch of courses. Expert 29, there are lots that I cover with DLOOKUP, lots of DLOOKUPs. Advanced lessons, developer lessons, it's one of those functions that's just so versatile, I cover it in tons of different classes. We will be doing more TechHelp videos on it as well.
But I hope you learned something today. That's your TechHelp video. Live long and prosper, my friends. Again, if you want to see that thing, post a comment down below and say yes, I want to see how to put that discount in the order total. All right, I'll see you next time.
TOPICS:
DLOOKUP with multiple criteria
Boolean logic in criteria strings
Handling string values in criteria
Constructing criteria strings for DLOOKUP
Combining criteria strings with concatenation
Using double double quotes in Access
Substituting field values in criteria strings
Putting criteria strings into DLOOKUP statements
Setting up a calculated field in Access queries
Using NZ function to handle null values
Correcting DLOOKUP errors with NZ
Verifying DLOOKUP results against a table
Handling lookup errors in queries
Incorporating calculated discounts in order forms
COMMERCIAL:
In today's video from Access Learning Zone, we're diving into part 2 of our DLOOKUP multiple criteria series. First, I'll recap how we set up our discount and order tables in part 1. Then, we'll tackle building the DLOOKUP criteria string, ensuring conditions like quantity and tier match correctly. I'll show you step-by-step how to encode string values, handle concatenations, and finally implement our DLOOKUP in a query. Plus, I'll cover using the NZ function to handle errors. It's a detailed guide to mastering DLOOKUP with multiple criteria. 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. When constructing a DLookUp criteria string, which of the following symbols is used to enclose a string value?
A. Single quotes (')
B. Double quotes (")
C. Pound signs (#)
D. Curly braces ({})
Q2. What function should you use in Access to handle null values and prevent errors in your DLookUp function?
A. ISNULL
B. ZEROIFNULL
C. NZ
D. IIF
Q3. In the example provided, if the tier 'A' needs to be used in the criteria string, how should it be represented within the string context?
A. Using single quotes around A ('A')
B. Using double quotes around A ("A")
C. Using double double quotes around A (""A"")
D. Without any quotes
Q4. When substituting the actual field names into the criteria string, where should these be placed?
A. Inside the existing string without any changes
B. Outside the original string, joined with concatenation
C. Enclosed in single quotes inside the string
D. Replaced directly in the string with no additional handling
Q5. Why is it important to handle null values in the units and tier fields when using DLookUp?
A. Null values always convert to zero automatically in Access.
B. DLookUp function does not differentiate between null and zero.
C. Null values in the criteria can cause the DLookUp function to fail and return an error.
D. Null values get automatically replaced by default values in Access.
Q6. Which of the following is an example of correct criteria for a DLookUp function using a string tier 'A' and units value?
A. "Units >= " & Units & " AND Units <= MaxQuantity AND Tier = '" & Tier & "'"
B. "Units >= Units AND Units <= MaxQuantity AND Tier = ""Tier"""
C. "Units >= "" & Units & "" AND Units <= "" & MaxQuantity & "" AND Tier = ""A"""
D. "Units >= " & Units & " AND Units <= MaxQuantity AND Tier = """ & Tier & """"
Q7. In the context of Access, what should dates be enclosed in within a criteria string?
A. Double quotes (")
B. Single quotes (')
C. Pound signs (#)
D. Curly braces ({})
Q8. When applying the NZ function to handle null values in the units field, what should be the correct approach as mentioned in the video?
A. Directly applying NZ function around the units field
B. NZ is not necessary around the units field
C. Wrapping the entire DLookUp criteria in NZ
D. Escaping the units with NZ inside the criteria string
Q9. Which string function allows you to handle concatenation within the criteria string in Access efficiently, as discussed in the video?
A. CONCAT
B. JOIN
C. &
D. +
Q10. Why should multiple conditions in a DLookUp function be enclosed in quotes when constructing criteria strings?
A. To improve readability
B. To ensure the entire condition is interpreted as a single string
C. To avoid the use of parentheses
D. To handle numeric values accurately
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-D; 7-C; 8-A; 9-C; 10-B
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 continues our exploration of DLookup with multiple criteria, and this is part 2 in the series. I never know exactly how many parts there will be until I'm finished, since some topics require more time or get more detailed as we dig into the material. If you have not watched part one yet, I recommend starting there first.
In our previous lesson, we looked at a sample grid for discounts, based on both the quantity ordered and a specific pricing tier. For each order, we take the number of units purchased and the corresponding tier, and then determine the correct discount by comparing these values to our discount table. We prepared everything with a discount table and an order table, and now it is time to focus on the mechanics of the DLookup function itself.
Let me first clarify the logic behind our criteria. Suppose we have an order of 12 units in tier A. What we want to check is that 12 is between the minimum and maximum quantities for the right tier. In other words, 12 must be greater than or equal to the minimum quantity and less than or equal to the maximum quantity, plus the tier itself must be "A". In Boolean logic, this forms a set of AND conditions.
When writing out this criteria as a string, remember that we're checking if the value (like 12) falls within a specified range and matches the correct tier. It is also important to note that Access requires string values (such as our tier) to be enclosed in double quotes, while date values have to be inside pound signs. Since the tier is a text field, make sure you use double quotes around "A".
Now, the entire DLookup criteria also must be a string itself. When writing a DLookup statement in Access, the criteria goes inside quotation marks. If you need to include quotes within your criteria string, you have to escape them by writing them as double double quotes. While Access will sometimes allow single quotes, I recommend against using them for reasons that I have explained in a separate video.
After building the general logic with sample values, the next step involves substituting the field names for those values. For example, we'll replace "12" with our Units field and bring those values outside of the string using concatenation. This works by closing the string, adding the field value, then reopening the string for further criteria. We use the same approach for the tier field, placing its value outside the string and wrapping it again in quotes as needed.
Once our criteria string is constructed, we're ready to implement it in Access. In a query, bring in your relevant order table fields, such as order ID, order date, units, and tier. Then, add a calculated field for your discount using DLookup. Select the discount value from the discount table and paste in your newly built criteria string. Run the query and verify that it returns the correct discount for each order. For example, placing "12" in tier "A" should return the correct discount, as should other values when you reference the discount table to check your work.
If any order does not match a record in the discount table, DLookup returns a null value which usually appears as an error in your query. To handle this, use the NZ function. NZ allows you to specify a default value, such as zero, in place of any null that would otherwise cause problems in your calculation. Remember to apply NZ not only to the result of your DLookup, but also to any fields that might be null, like Units or Tier, since you cannot look up a null in the table.
After wrapping your values with NZ (converting null units to zero, for example, and a null tier to an empty string), save your query and run it again. Now, any orders with no discount will simply show a zero without error messages.
This construction of a DLookup with multiple criteria is a complex process that takes a good deal of practice. I remember very well how confusing it was when I first started working with these techniques. If you want to cement your understanding, try teaching the concept to someone else—when you can explain it, it really helps reinforce your own learning.
I know many students have scenarios similar to what we have covered today. For example, someone recently asked about looking up a field in a table where the quantity falls between a range and matches a certain tier, which follows the same pattern we just built. The key is to set the right logic in your criteria string and substitute actual field names for sample numbers.
If you are wondering how to take this discount and apply it directly to your order forms or include it in calculations of order totals, let me know in the comments. If there is enough interest I will make another lesson to show you that complete process.
DLookup is extremely versatile and powerful in Access. I cover it in greater detail in many of my Expert and Advanced series courses, so for deeper dives and variations make sure to check out those resources as well.
You can find a 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 DLOOKUP with multiple criteria
Boolean logic in criteria strings
Handling string values in criteria
Constructing criteria strings for DLOOKUP
Combining criteria strings with concatenation
Using double double quotes in Access
Substituting field values in criteria strings
Putting criteria strings into DLOOKUP statements
Setting up a calculated field in Access queries
Using NZ function to handle null values
Correcting DLOOKUP errors with NZ
Verifying DLOOKUP results against a table
Handling lookup errors in queries |
