DateDiff Previous Record
By Richard Rost
9 months ago
Calculate Date Differences Between Records in Access In this Microsoft Access tutorial, I will show you how to calculate the difference in dates between a record and the previous record. We'll start by using the easy method with the DMax function for quick calculations, though it may be slower for larger datasets. You'll learn how to create a query to determine the time difference between consecutive orders for a customer. We'll address common issues such as handling duplicate contact dates, setting up your query without errors, and why the DMax method is best suited for smaller datasets. Zoe from Springfield, Missouri (a Platinum Member) asks: How can I figure out the time between two consecutive orders for a customer in Microsoft Access? I've got this list of orders and dates but I'm not sure how to calculate the days between them like I do in Excel. For instance, if the first order was on March 1st and the next one was on March 3rd, I'd want it to show there's a 2 day difference. I'm also curious about what happens if two orders are on the same date. Any ideas on how to get started with this in Access? MembersIn the extended cut, we will learn how to calculate the date differences using two subqueries instead of the DMax method. I will show you how to select the previous contact date using subqueries for a more efficient calculation, which is especially useful for large datasets. Additionally, we will address the issue of handling records with identical dates by ensuring the date difference shows zero without including the current record itself. This approach involves writing a more complex query that accurately handles duplicate contact dates and performs the calculations efficiently. 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, date differences, consecutive records, DMax function, time between orders, calculated fields, Nz function, CDate function, subquery usage, ContactDate, PreviousContactDate, domain aggregate functions, subquery optimization, identical date issues, expert-level Access tips
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, we're doing date differences, but not just any date difference, and we're not even going to use the DateDiff function. We're going to figure out how to calculate the differences between two dates or date times or whatever between two consecutive records in a table, which adds an extra level of complexity.
Today's question comes from Zoe in Springfield, Missouri, one of my platinum members. Zoe says, "How can I figure out the time between two consecutive orders for a customer in Microsoft Access? I've got this list of orders and dates, but I'm not sure how to calculate the days between them like I do in Excel. For instance, if the first order was on March 1st and the second was on March 3rd, I'd want to show a two-day difference. I'm also curious about what happens if two orders are on the same date. Any ideas on how to get started with this in Access?"
Well, this is not exactly easy to do in Access, Zoe. Most things are possible, but some things are a little more complicated. Excel makes things really easy to do because it works in rows and columns, but Access works in records and fields, which is different.
So how do we do this in Excel? Well, Excel is pretty straightforward. I've got a contact date over here. And if I want to see what the difference between those two days is, I just subtract them. Just like in Access, dates in Excel where a value of one equals one whole day. So if I come over here and put a "days difference" or just "days," now, we can't do one in the first row, but the second row we can say this equals this value minus that value, right? A7 minus A6, and I press enter. Well, that's zero because those are the same, but now if I take this and grab the auto-fill handle and pull it down, that pulls the formula down. I teach this in some Excel classes, by the way, if you're not familiar with this. By the time you get to this one, A9 minus A8 is subtracted, and the value is three. That's easy to do because in Excel, you can reference the row, A9 minus A8. You can't exactly do that in Access.
So how do we do that in Access? Before we get into it, this is an expert-level video. You don't need to be a developer. You don't need VBA to do this. It's just functions and stuff, but it's a little bit more than the beginner level, so if you haven't taken my beginner courses, go watch those. Here are some other videos that will help you before you start watching this one.
We're going to use DMax because we have to find the largest date in the table, the largest date that's less than today's date. That's how you find the previous one. So we'll do that with DMax. You should understand how date math works in Access. Just talked about it a moment ago. It's the same as in Excel: a value of one equals one day, so two is two days, seven is a week. If I say a value of 0.5, that's 12 hours, half a day. So go watch this if you want to learn more about that. I do have a video on the actual DateDiff function here, but it won't help you with this example, so don't worry about it. This is good if you've got values you want to check within a record. You could use this after we do the calculation if you want. I'll talk about it a little bit later.
You will need to know how to create calculated fields in queries, and we will use the CDate function, so go brush up on your type conversion functions if you want. This just converts one data type to another because sometimes the domain functions like DMax will return a string, and we have to convert it over to a date. So this video explains that. These are all free videos on my website or on my YouTube channel. But watch those and then come back.
This is my TechHelp free template. This is a free database you can grab a copy of on my website if you want to. I know in your question you mentioned you're using orders. I'm just going to use contacts because I have a lot more data in this table. But it works with any table and any field. It doesn't really matter. We could use orders too if you want, but I'm going to stick with contacts.
In my contact table, I got a contact date, and I want to figure out what the difference in time is. We'll use the difference in days, but you can change it later if you want. Between this record and this record, now the first record obviously we don't have a record before that, so we'll deal with that null later. But this guy here, what I need to do is I need to look in the table and find the largest value that's less than that. To do that, we'll use DMax because when we get down to here, it's going to say, "Okay, I need to find the largest contact date that's less than this," and that should give you this one. You can't rely on the IDs. First of all, if you sort this by ID, they're in different orders because records aren't always entered in chronological order. So you have to look at the date if you want to get the contact that comes before this date. Remember, auto numbers are not for you. They're for internal purposes. They're only used for relationships. That's it. And to make sure that records are unique.
Let's create a query.
They've changed this now. Create - Query Design. In Query Designer, I'm going to bring in that contact table. That's all we need. I'll bring in the ContactID. Let me make this a little bit bigger. ContactID, we'll bring in the ContactDate, and then I'm going to make a calculated field over here called PreviousContactDate. So I'm going to click right there where my calculated field is going to go. I'm going to shift F2 to zoom in so you can see this better.
And it's going to be, let's call it PreviousContactDate. And that's going to be, here's where our DMax comes in: give me DMax of the ContactDate from the Contact table where, here's our criteria, where the ContactDate is less than the current ContactDate in this record. Now remember, dates have to be inside of these pound symbols, right hashtags, octothorpes if you prefer. Then we'll add on to that ContactDate and close up the hashtags and end your DMax statement.
Look up the largest ContactDate from the Contact table where the ContactDate is less than the current ContactDate in this record. So whatever it is, January 1st, 2004, will be put in here, and that will look like an actual date. That's a little string concatenation there. If you don't know how to use that, I'll put a link to a video down below about concatenation. Let's hit okay and run it. There we go.
See that? Except for the first row because there is no record before it is actually a null there. It's going to cause some problems in just a second, but we'll deal with that in a minute. While we're at it, let's sort by ContactDate ascending. Okay, and let's save this as my PreviousContactQuery, or whatever you want to call it. Run it again. There we go. Now they're actually sorted properly. This one should be showing this record up here and it is, and likewise down the line because these are all sorted by date. Everybody has got the right date there. Beautiful. We're off to a good start.
Now let's deal with that null value because that first record is going to have a null value. We had that in Excel, but we have to do more calculations. You have to subtract the state from that date, and that null value is going to cause problems, so let's use our Nz function.
Nz converts null values to whatever value you want, Nz because it's null to zero usually, but you can put something else in there if you want to, and if you don't know how to use Nz, again, I've got a video on it. I'll put a link down below. So we're just going to say here we're going to say Nz - Null to zero - that means if the result of this is null, which that first record is always going to be, what value do you want to give it? You can make it zero if you want to, but that's the zero date then, and that's like 1899, December 30th, or something weird like that. So why don't we instead say today's date, put in here the actual ContactDate itself. So the first record, we'll say the previous record is just the ContactDate, see? So if it doesn't exist, just use your own value. That's what I would do. Run it, and there you go. See, the first record just has itself. So the difference over here will be zero. That's up to you if you want to go from the start date of the company or well, put whatever value you want in there. These are your Legos. You build however you want.
Now the next problem you'll see if you've been with me for a while and watched any of my classes or previous TechHelp videos, you'll see a problem right away. You'll see that ContactDate is lined up on the right side of this column, whereas PreviousContactDate is lined up on the left side of the column. What does that tell you?
Well, numeric value types like dates, numbers, currency values, all line up on the right side of the column. Text strings line up on the left. Now, why is DMax returning a string value? It just does sometimes. It's one of the quirks, one of the weird things about Access is those domain aggregate functions don't always return the right value because it has to look at all of these values coming in. Usually, it only looks at the first one, and in this case, the first one is always going to be a null value, so it has no idea what to do with it. It just assumes string.
The domain lookup functions, max, sum, all those, right, they look at the usually the first value and try to decide what the proper data type is. So in this case, we have to tell it, we have to force it to use a particular data type, and that's why you wanted to watch those type conversion functions.
So now we have to take this whole thing and convert it to a date with CDate. Don't forget to put your closed parenthesis on the end. Save it, run it, there we go. That looks better. Now those are actual date values because before, if you try to do some subtraction, you'd get an error message. I was going to put it in the video, but I figured this video is long enough already.
Now I'm going to shrink this up. We're good with that. Now we're ready to do the actual subtraction to calculate the days difference between these two. So I'm going to come in here, zoom in again.
DaysDiff is going to be equal to the ContactDate minus the value we just created, the PreviousContactDate. Right, bigger minus smaller always. Run it, there you go. There's your DaysDiff. This value is 3,140 days after the first one, that seems about right, 1994 to 2002.
Let's find something else here. There's 43 days. That's about right. July to September, yeah, it's okay. We're looking good. This one's less than a day. 2.223. Perfect. Now you could use DateDiff here if you know how to use DateDiff instead of just doing a basic subtraction. You could put DateDiff here if you want hours or seconds or months or whatever, use DateDiff here and use these as your two different dates. I'm not going to go through it. You can watch my DateDiff video for more information.
One thing you might want to do is format this if you are going to keep this as a value because you got all these weird things in here. Now, here's another quirk with Access, and I hate this. If you go into the properties for this column, if you set decimal places to one, it ignores you. It doesn't care. It just, I don't care. I don't give a whatever. It doesn't care if you put a set of decimal places in here. Sammy, add that to the list. I don't know why. It just doesn't care. What I always do is I just leave that blank or you can leave it one if you want, but it's going to ignore you. In the format, put 0.0. That says give me one decimal place basically. I have a whole separate video on the format property and the format function. You can go watch that if you want to as well. I'll put a link to it down below. Run it.Yeah, I got one decimal place. All right, and of course, this is a number of days. You can do other math in here if you want weeks or use date diff or whatever you want to do.
Now, if you've watched any of my previous videos on these functions like demax, dlookup, and the average I just did a few days ago, you'll know that I say that you should try to avoid them in queries if possible. This is the easy way to do this, but it's not the fastest way because it has to calculate that dmax with each record going down the list. Now, I only have 104 records, but if you get 104,000 records and you're pulling them across the network or even over the internet, this will run extremely slow.
The best way to do this is with something called a subquery. I do have another video I just released on subqueries. You're welcome to go watch that and see if you figure it out on your own. Or, if you remember, stick around and I will show you how to do the subquery in the extended cut. Silver members and up get access to all of my extended cut videos, not just this one, all of them.
We'll also talk about the two identical dates problem. If you got two or three dates in there and these happen to be the same exact date, you are going to run into a problem. Let's change to this one here, that one, that one, that one, see? These guys are all zero now, and that works for these because it's the first record. But what if we do a different one? What if we say this one here is 2021 11, okay, and then I copy this now, copy, paste, paste, paste, paste, see?
Now, each of these, instead of saying the previous one is zero, it's saying it's 214 days from the previous one, and that's not right. It's exactly the same; this should be a zero because the previous one is on the same date, so that's a problem. We'll see how to deal with that in the extended cut as well.
If you like learning this stuff and this is on your level and you're enjoying it, check out my website. I have tons more expert lessons like this where you can learn a lot more about Microsoft Access without having to be a programmer. I know a lot of people tell me, "Hey man, I love working with Access. I like building databases, but I don't want to learn VBA. I'm not a programmer." Okay, fine. I've got lots of lessons for you. There's tons you can do with Access without having to be a programmer. It's one of its strengths.
All right, but that is going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I will see you in the extended cut.
TOPICS:
Calculating date differences between records
Finding time between consecutive orders
Understanding date math in Access
Using DMax to find previous date in a table
Creating calculated fields in queries
Using CDate for type conversion
Handling null values with Nz function
Creating a query in Access
String concatenation in DMax
Solving alignment issue with CDate
Subtracting dates for days difference
Formatting days difference with decimal places
Understanding quirks of domain functions in Access
COMMERCIAL:
In today's video, we're tackling the challenge of calculating date differences between consecutive records in Microsoft Access without using the DateDiff function. You'll learn how to find the time between orders using functions like DMax and Nz, overcoming the complexities of record-based calculations. We'll also discuss the pitfalls of null values and text string returns and how to address them. Additionally, you'll understand formatting solutions for these differences. Also, in today's Extended Cut, we'll solve issues with identical dates and explore optimizing queries with subqueries for better performance. 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 the main goal of the tutorial?
A. To understand the DateDiff function in Access
B. To calculate the difference between two dates in consecutive records
C. To learn how to use VBA in Access
D. To explore using macros in Access
Q2. What is a major difference between Excel and Access mentioned in the tutorial?
A. Access can automatically calculate date differences
B. Excel uses records and fields, Access uses rows and columns
C. Excel works in rows and columns, Access works in records and fields
D. Access cannot perform date calculations
Q3. Why can't you rely on IDs when calculating date differences in Access?
A. IDs are always in chronological order
B. Auto numbers are intended for internal purposes, not for user calculations
C. IDs should be used for calculations instead of dates
D. IDs automatically correct chronological errors
Q4. Which function do we use to find the previous date in a record set?
A. DCount
B. DLookup
C. DFirst
D. DMax
Q5. What does the Nz function do when working with null values?
A. It converts null values to a specified value
B. It sorts records by date
C. It creates new fields in a table
D. It automatically fills down values
Q6. Why do domain aggregate functions sometimes return incorrect data types in Access?
A. They always return the correct data type
B. They rely on user input
C. They assume string data types if the first value is null
D. They convert all data types to dates
Q7. How do you ensure a domain aggregate function returns the correct data type in Access?
A. Use the DateAdd function
B. Use the CDate function to convert the data type
C. Accept any default data type given by the function
D. Manually sort the data before using the function
Q8. If two orders are on the same date, what issue may arise discussed in the video?
A. The calculated difference is negative
B. Access crashes entirely
C. The calculation shows a non-zero difference
D. The calculation throws an error
Q9. What potential problem arises from using domain aggregate functions such as DMax in queries with large datasets?
A. Queries will yield incorrect results
B. Queries will run slower, especially with large datasets
C. Domain functions can't be used with tables over 10,000 records
D. The text alignment will automatically change
Q10. What content does the extended cut of the video cover?
A. How to convert data into VBA scripts
B. Advanced formatting options in Access
C. Solving problems with identical dates and using subqueries
D. Using Excel in conjunction with Access
Answers: 1-B; 2-C; 3-B; 4-D; 5-A; 6-C; 7-B; 8-C; 9-B; 10-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 is presented by me, Richard Rost. In this lesson, we'll explore how to calculate date differences between consecutive records in a table without using the DateDiff function in Microsoft Access. This method is particularly useful when you want to find the time interval between consecutive customer orders or any similar situation.
Zoe from Springfield, Missouri, one of my platinum members, posed a question on how to calculate the days between consecutive orders for a customer in Access, similar to how it's done in Excel. She wanted to know how to show a two-day difference if one order was placed on March 1st and another on March 3rd, and was curious about handling orders made on the same date.
To achieve this in Access, we'll take a more advanced approach, as it's not as straightforward as in Excel. In Excel, subtracting dates directly in rows is simple because of its tabular format, but Access requires working with records and fields.
The solution in Access involves using the DMax function to identify the most recent date that precedes the current record's date. To perform accurate date calculations, you should have a good understanding of how date math works in Access. It's important to know that a value of 1 represents one day, 0.5 is twelve hours, and so on, similar to how it's done in Excel.
In this tutorial, we'll create a query that pulls data from our contact table. We'll introduce a calculated field named PreviousContactDate utilizing the DMax function. This function will help us find the largest contact date that's less than the current date in the record we are examining. Remember, to properly handle dates, they'll be enclosed in pound symbols (#).
In the first row of our query's output, where no previous record exists, you'll encounter a null value. We'll handle this using the Nz function, which allows us to substitute the null with a date value you desire. For simplicity, you might replace it with the current ContactDate itself to avoid calculation errors.
An additional challenge is ensuring that all dates appear as actual date values rather than strings. To do this, we'll employ the CDate function, which converts string values back into date format, allowing for proper arithmetic operations.
To find the interval between consecutive records, subtract the previous date from the current one. While this approach works effectively in most cases, there may be issues with identical dates showing non-zero differences, which we'll address using a subquery method in the extended lesson.
When you have a large dataset, remember that using domain aggregate functions like DMax in queries might slow down performance since they calculate separately for each record. For speeding up the process, transitioning to subqueries is advisable.
In today's extended cut, available to Silver members and above, we'll dive deeper into this topic by exploring the use of subqueries to optimize calculations in larger databases and address problems that arise from having identical dates.
For more advanced lessons on Microsoft Access, you can visit my website, where you can find tutorials designed to enhance your database skills without delving into programming or VBA.
And that's it for today's TechHelp tutorial. 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
Calculating date differences between records
Finding time between consecutive orders
Understanding date math in Access
Using DMax to find previous date in a table
Creating calculated fields in queries
Using CDate for type conversion
Handling null values with Nz function
Creating a query in Access
String concatenation in DMax
Solving alignment issue with CDate
Subtracting dates for days difference
Formatting days difference with decimal places
Understanding quirks of domain functions in Access
|
