DMax
By Richard Rost
5 years ago
Using the DMax Function to Get the Largest Value
Learn how to use the Dmax function to quickly show the customer's last order date on the Customer Form. This way you don't have to open up the Order Form to find it.
Elisa from Montgomery, Alabama (a Platinum Member) asks: I'm using your awesome free TechHelp Customer Template. Thank you. Is there a way I can quickly see the date of the customer's last order from the Customer Form without having to open the Order Form?
Members
I'll show you how to use Dmax to calculate the number of days between orders in a query.
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
Subscribe to DMax
Get notifications when this page is updated
Intro
In this video, I will show you how to use the DMax function in Microsoft Access to display the last order date for a customer directly on the customer form. We will walk through setting up a calculated field to quickly view the most recent order without opening the order form, discuss how to apply DMax with criteria based on customer ID, and explain important tips for working with dates and text in your criteria. This tutorial is perfect if you want a fast way to see the latest order date at a glance on your customer screen.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to use the DMax function to, in this case, show the last order date on the customer form in Microsoft Access. When you open up a customer's form, you want to quickly, at a glance, see what the maximum date of their last order was. We will use the DMax function for that. I will show you how in this video.
This question comes from Alisa in Montgomery, Alabama. I hope I am pronouncing that right. She asks, I am using your awesome free TechHelp customer template. Thank you. You are welcome. Is there a way that I can quickly see the date of the customer's last order from the customer form without having to open the order form?
Well, of course, Alisa. You just have to learn how to use something called the DMax function.
So here I am in the awesome TechHelp free template. This is the customer template she is talking about. You can grab a copy of this yourself if you want to. It is on my website. I will put a link down below in the template section.
We have got a basic customer list here. If you click on one of these guys, you open up the customer record. What Alisa is talking about is, in order to see this person's last order, you have got to open up the order form. These are generally sorted so that the most recent orders are first, but it would be nice to just see right here if you have an order, what the order date is, what the last order date was.
Maybe if you are on a sales call, you open up the customer's record and you can see, boom, their last date that they placed an order was two months ago, without having to open this up and look through here.
So in order to do that, we are going to use a function called the DMax function.
Now, the DMax function takes two or optionally three bits of information. What is the field you are looking for, where is the table or query that you are looking in, and optionally some criteria, for example, this customer ID.
So, for example, this says DMax order date from order T, and yes, those have to be inside of quotes like that. That says find me the largest order date from the order table. There is no how, so it is just going to go into the order table and find the largest order date, the most recent order that is in your system for all customers.
Now this one says find the largest order date in the order table where the customer ID equals three. So, whatever customer happens to be customer three, find his largest order date. I know my font keeps getting smaller as this line gets longer.
This next one says find the order date, the maximum order date from the order table where the customer ID equals whatever the customer ID happens to be on the current form. So that customer ID there on the very end gets treated as a field name, and if you are working with a form, or even a query result, it will say go get the current customer ID and put it here. The little ampersand is for string concatenation. It puts two strings together.
If you have never done concatenation before, go look in the links section down below the video and watch my concatenation video.
So that is what we are going to use right there, that last one, in order to get the largest order date from the order table where the customer ID equals the currently open customer.
Let's go put it in the form.
All right, let's go to design view. You can put it anywhere you want to. I am just going to put it up top here, right up top.
Let's go to design, let's grab a text box right there, drop it over here somewhere. Now it is unbound. We are going to double-click on this guy.
Unbound means it is not getting its data from the underlying table or query, which if you click here in the form property, you will see it is customer T. So this is not a value in the customer T. This is a calculated field that we are going to work with right here directly in the form. That means you cannot change its value.
I like to do this with fields you cannot change the value of. I like to make them gray. This just kind of visually signifies to the user, hey, you cannot change that, kind of like I did with the customer ID over here.
So let's change the label. We will put in here last order. Maybe make that black so you can actually read it. Let's go to black and we will slide this over here.
So now what do we do with this box? Well, let's go find the control source. Actually, first let's give it a name. It is text 30 right now. I do not like text 30. Let's make sure we name all of our objects. Let's call this last order date.
Now the control source is where the magic happens, and we have already actually typed it in.
All right, I am going to zoom in and Shift F2 so you can see what I am typing here. This is going to be equals DMax. What am I DMaxing? The order date from the order T, comma, where the customer ID equals the current customer ID on the order form. That is it. That is all you need.
Hit OK. That puts it right there in the control source. Save it. Let's close that. Let's close this. Let's open it back up again. And there we go. 1/14/2021. Let's go in here and make sure you. There it is.
Let's put another order in the system. Here is one from today, 2/12. Put it in for me. Put a product in here, blah, blah, blah, blah. Let's close it.
Now this will not refresh unless you leave the form and come back to it. Now you can see 2/12 is the latest order. This is not sorted anyway. I did not sort this, so you would have to come in here and go to the end.
But that is a nice, handy way when you open the customer form to see that.
Can you do it so when you close this, this refreshes? That involves a little more programming. Members, I will show you how to do that in the extended cut.
A couple more things to mention about DMax, though. First thing, remember if your criteria involves a date, then you have to make sure that you enclose that date inside of hashtags or octothorpes or pound signs, whatever you want to call them.
This DMax says go to the order table, find me the largest order total where the order date equals January 1, 2021. So, in other words, for that date, find the largest order total. And since the date is the criteria, we have to make sure that we enclose it inside of octothorpes, hashtags, whatever you want to call them.
Here is another one. If your criteria involves a text string, you have to make sure you enclose it in either double, double quotes or single quotes.
This one says, find me the largest order total from the order T where the state equals New York. So, all of my orders from New York, find the largest one. Now, I highlighted the single quotes there because they are difficult to see when they are next to the double quotes, but I do not like using single quotes. I know with SQL Server and something like that, you have to use them, but in Access, I try to use double, double quotes.
So that becomes this. That puts double quotes inside of double quotes. I know it seems confusing. I have a whole video on this. It is called the double, double quote problem, so watch that. I will put a link down below.
The reason why I do not like single quotes is because single quotes can sometimes be found inside of last names, for example. There is a last name where there is a single quote in it. So that will not work. That will cause a problem with your DMax. That fixes the problem.
You can also use ANDs and ORs and things like that in your criteria. Like, this one says, find me the largest order total from the order T where the state equals New York or the state equals Florida. So that kind of stuff is allowed as well.
I covered DMax and a lot more of the D functions in detail in my Expert 29 class. There is a whole bunch of them. There is the granddaddy of them all, DLookup, which is my favorite one. There is DSum, DCount, DAverage, DMax, DMin, First, Last, a whole ton of them. I spent over two hours going over all these different functions in Access Expert 29.
To learn more about DMax, in the extended cut for members, I show you how to calculate the days since the last order for each order in a query. For example, you have your list of order dates, January 5, January 10, January 17, and so on. In a query, you want to quickly see how many days there were between this order and the one before it. So you can see the one on the 10th, there were five days since the one before that, and so on.
That is in the extended cut. Extended cut is for Silver members on up. Members get access to all of my previous extended cut videos as well.
How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available.
Silver members on up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use.
Platinum members get all of the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all.
Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar. It is also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.
Quiz
Q1. What is the primary use of the DMax function as demonstrated in the video?
A. To delete the oldest order from the order table
B. To find the maximum (latest) order date for a customer
C. To sum all order amounts for a customer
D. To search for the customer's name
Q2. In the example shown, why use DMax on the customer form?
A. To allow editing of previous orders directly
B. To quickly view the customer's last order date without opening the order form
C. To add new customers automatically
D. To display the customer's contact information
Q3. What are the required arguments for the DMax function?
A. Table name only
B. Field name, table/query name, and optional criteria
C. Table name, form name, and report name
D. Field name and report name
Q4. What does the criteria argument in DMax allow you to do?
A. Change the order of data in the table
B. Specify which records to consider when finding the maximum value
C. Rename fields in a table
D. Add new fields to a query
Q5. In Microsoft Access, how do you reference the current customer ID in a form when using DMax?
A. By typing the customer's name
B. Using a hardcoded value
C. By concatenating the field value into the criteria string
D. Using the report header property
Q6. What is the main purpose of marking a calculated field (like the DMax last order date) as gray in the form?
A. To highlight it as editable
B. To hide it from the user
C. To show it is a calculated, read-only value
D. To set it as a default field
Q7. When using DMax with date criteria, what must you enclose the date value with in your criteria string?
A. Parentheses
B. Square brackets
C. Hashtags or pound signs (#)
D. Curly braces
Q8. How should you handle text criteria in DMax?
A. Use single quotes only
B. Use double, double quotes for text strings inside criteria
C. Never use quotes of any kind
D. Use hashtags
Q9. What problem can occur if you use single quotes for text criteria in DMax?
A. The database will not run
B. Single quotes may appear in user data, breaking the criteria
C. Data will be formatted incorrectly
D. Query result will always be empty
Q10. Which of the following additional D functions does the instructor mention alongside DMax?
A. DField and DRecord
B. DSum, DCount, DAverage, DMin, and DLookup
C. DForm and DSort
D. DTable and DConcat
Q11. If a user adds a new order, how does the last order date field on the customer form update?
A. Automatically as soon as the order is entered
B. Only after leaving and returning to the form
C. Only when Access is restarted
D. Only after running a manual update query
Q12. What does concatenation (with ampersand &) achieve in the DMax criteria string?
A. It removes extra spaces from the string
B. It combines strings or field values to build the criteria
C. It converts data into numeric format
D. It hides confidential data
Q13. According to the video, what Access feature allows the combination of multiple conditions in DMax criteria?
A. Order By clause
B. Using AND and OR in the criteria string
C. Group By clause
D. Sorting by multiple columns
Q14. Where can you find more information about the double, double quote problem mentioned in the video?
A. Only in Access's official documentation
B. In a specific video linked below the main video
C. It is not explained anywhere
D. In the comments section only
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-C; 7-C; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-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 video from Access Learning Zone covers how to utilize the DMax function in Microsoft Access to display the most recent order date directly on the customer form. This allows you to quickly see when a customer last placed an order without having to open up the orders form.
This question came from someone using my free TechHelp customer template who wanted an easy way to see the last order date while viewing a customer record. Of course, this can be accomplished using the DMax function.
The customer template provides a simple list of customers. When you select a customer, you see their details, but to find out when they last placed an order, you would typically need to open the order form. For efficiency, especially during a sales call, it is helpful to see the date of their last order right on the main customer screen. DMax is perfect for this.
The DMax function asks for a few bits of information: the field you are interested in, the table or query it is stored in, and optionally, some criteria. For our purposes, we want to find the largest value (the most recent date) in the order date field, filtered for the current customer. This tells you the date of their latest order.
For example, using DMax with just the field and table returns the most recent order date for your entire database, not limited by customer. Including criteria, such as CustomerID equals a specific value, will target the query to the customer of interest. Using the field from the current form pulls whatever customer you are viewing at that moment.
If you are not familiar with string concatenation in Access, you can check my other resources for details on how that works. Essentially, this technique lets you build a criteria string on the fly based on which customer's record is open at the time.
To set this up, go into design view of the customer form. Add a text box wherever you like, such as near the top to make it prominent. Since this field is calculated and not stored in the underlying customer table, you will want to make it unbound. It is helpful to shade such fields gray so users recognize they cannot edit them, just as with fields like CustomerID.
Update the label to say something like "Last Order" and adjust its appearance as needed. Rename the text box to something more descriptive, such as last order date, for clarity.
The key step is setting the Control Source property of this text box. Enter a DMax expression that finds the maximum order date for the order table where the customer ID matches that of the current form. This will automatically update the display to always show the last order date for the customer being viewed.
After saving and closing the form, reopening it will show the correct last order date. If a new order is added, the label will not refresh immediately unless the form is closed and reopened; live updates require a bit more advanced programming. In the extended cut for members, I demonstrate how to have the date refresh automatically when records change.
There are some important points to keep in mind when working with DMax. If you use a date as criteria, be sure to enclose that date value in pound signs (#) so Access reads it correctly. For instance, looking up the largest order total for a specific date needs pound signs around the date.
If using a text string as criteria, the string must be surrounded by double quotes inside the overall string. Single quotes can be problematic, especially if your data might contain apostrophes (for example, in customer names), so I recommend always using nested double quotes. I have another video on handling double quotes in criteria, which you can view for more detail.
You can also include multiple conditions, such as searching for orders in either New York or Florida by using "OR" in the criteria. This approach works with DMax and other domain aggregate functions.
If you are interested in a more comprehensive look at the D functions in Access, I go into significant detail in my Expert 29 class, covering DLookup, DSum, DCount, DAverage, DMax, DMin, First, Last, and others over several hours of lessons.
Additionally, in today's Extended Cut for members, I explain how to calculate the number of days since the last order for each entry in an order query. This lets you produce a running history of days elapsed between orders, which can be very useful for analysis.
Members have several ways to benefit from extended content based on their membership level. Silver members and above get access to the extended cut TechHelp videos, as well as live chat sessions. Gold members can download all TechHelp sample databases, plus get into my Code Vault with an extensive library of reusable functions. Platinum membership adds access to my entire library of full beginner and some expert courses across multiple Microsoft Office and programming products.
Free TechHelp videos will always continue. I enjoy making them as long as you find them useful. If you appreciate the content, please like the video and leave a comment. I do read and appreciate all your feedback.
Make sure to subscribe to my channel for free and enable notifications, so you always know when new material is released. For resources and further information, check the links available beneath the video, including bonus lessons and email notification sign up.
If you have not yet taken my Access Level 1 course, it is completely free and covers all the essentials of database creation with Access. It is over three hours long and can be found both on my website and YouTube channel. Level 2 is just one dollar or free for all YouTube channel members.
If you want your own question answered, just visit my TechHelp page to submit it.
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
Using DMax to display last order date on a customer form
Setting up an unbound calculated field in Access forms
Building DMax criteria referencing the current form record
Concatenating field values into DMax criteria in Access
Formatting unbound text box controls for calculated values
Assigning a control source to show the last order date
Testing and verifying the DMax function on customer records
Handling date criteria with hashtags in DMax
Handling text criteria with double double quotes in DMax
Using logical operators like AND/OR in DMax criteria
Naming and labeling form controls for clarity
|
