Fitness 37
By Richard Rost
7 months ago
Finding the Most Recent Entry on a Specific Date In this Microsoft Access tutorial, I will show you how to modify your fitness database to ensure that when you add an item to a previous date, it is inserted at the end of that day's log instead of the beginning. You will learn how to use the DMax function to find the latest entry for a specific date, handle potential edge cases involving date and time, and prevent errors when adding items with an empty combo box. This is part 37. 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 CoursesUp Next
Keywords TechHelp Access, fitness database, DMax function, date time value, food log table, max date time, union query, dateadd function, edge case handling, combo box validation, food entry order, debug compile, food item tracking, personal database, user input error, sequential log entries
Subscribe to Fitness 37
Get notifications when this page is updated
Intro In this video, we'll continue building our Microsoft Access fitness database by learning how to find the most recent entry on a specific date and ensure that new entries added to previous days are placed in the correct order. We'll work with the DMax function to get the latest log entry, use NZ to handle null values, and add a second to date-time values to maintain sequence. We'll also look at how to prevent date overflow, manage edge cases, and handle null combo box entries to avoid errors. This is part 37.Transcript Today's part 37 of my fitness database series. Whether or not you're building a database to track your fitness, this is a video on building databases. So you're going to learn some cool tips and tricks. If you haven't watched parts 1 through 36, go watch those first so you know what we're doing.
Today we're going to figure out what the most recent entry was on a specific date. If we add an item to a previous date, it adds it at the end instead of at the beginning. So let's get to it.
Alrighty, and we're back. Let's pick up where we left off yesterday, and when we add items to a previous day, right now they're just coming in at midnight. Let's figure out what the last item on that day is and add it after that. If it's midnight, that's fine. But I want it to be at the bottom, not at the top. That doesn't make sense.
Alright, so let's go into our proper log date time function, which we can follow. We'll follow the noodles. Follow the noodles. Add food item in the log. If you can't remember the name of the function, just drill down to it. There it is right there. I happen to be here, but I would have found it with this and then come up here.
So if we're on the same date, that's fine. Entry for today. We'll leave that alone. Otherwise, entry for a different day. In here, instead of just setting it even log date, let's figure out what the max date time of an item on that day is.
So we'll need a date time value. We'll call it FDT food date time, whatever you want to call it, as a date.
Now we're going to say FDT equals, we're going to use the DMax function because we want the largest of something. We want the largest date time on that date. So we have to chew on it in between whatever values are in the log date up top. If one doesn't exist, then we'll just return that value at midnight. So we'll use NZ in case it's null.
DMax, and if you're not familiar with DMax and you got this far in the series, go watch my DMax video. You'll find that on my website. Do a search for it.
By this point in the series, I don't hold your hand anymore unless it's something totally new. We're going to be doing a union query soon and I haven't done that in the series yet. But I'm pretty sure we've done DMax.
So, what are we looking up? We're looking up a food date time from the food log table. It's another item in the log where the food date time is greater than or equal to the log date. Now, the log date could have a time in it, so we're going to take the date value of it. So inside of pound signs, it's going to be the date value of... let's make this another line.
Let's close. We don't need you for a new often. The date value of the log date and close the pound. Now we'll make it another line. I hate doing that where you break it inside of those things. I'll have to get all that on the same line.
So it's got to be greater than or equal to that value there, and the food date time has to be less than that plus one. So it's date value log date plus one and my thingy.
Just in case there are no entries on that date, we're going to give it date value log date so it will give it midnight on that date. And something's typed out one by this, probably a parenthesis somewhere. Let's see here.
Now I found it. It's a quote. Anybody see it? Pause the video. Try to find it yourself if you can. I know this thing can get crazy. I always start from the beginning and look. OK. Enzy. There's that open parenthesis. I wish Access had that built in where if you highlighted one of these, it would show you the closing one. And it's not a parenthesis.
There's that closing one. Here's the DMax and here's its closing one. Then we got food date time. Food log. Oh right there. Think. Think. It would be nice for the Access editor if it had that. You could select this and it would show you the closing one.
So, now we've got the food date time of the largest item on that date. So we'll say find largest or max date time on specified date. Whatever. I've never been great at writing comments.
Now what we're going to do is we're going to add one second to that date so we make sure it's after the last one. So the value we're returning is going to be... where are we? Oh, the proper log date equals... get rid of this... we're going to say date add a second. How many seconds? One second. So what value? FDT. Food date time. So it will be one second after the last item on that date. And if it isn't, it will be midnight plus one second.
Now as I was writing this, I was thinking to myself, possibility, it's possible with this algorithm, if you're adding it at 11:59:59 pm, it's possible to push it a date too far. So we have to check for that too. This will almost never come up. We're just going to say if the date that we have now is greater than the log date, just back it up a second.
So if the date value that we end up with, a proper log date time, is greater than the date value of the log date, that's the guy up top, then we went too far, bring it back one second. So we'll just say proper log date time equals the date value of the log date. Here's a cool thing you can do. You can say plus. I'm going to add to the log date time 11:59:59 pm. Because we're already... that basically says we're at midnight and the next day, we added a second too far. We have to bring it back.
So, might those last few items, if that's the case, be slightly out of order? Yeah. If you're adding stuff at 11:59:59, that's an edge case. That should almost never happen.
Let's test it. Save it. Debug compile. I laughed a few times when I was writing this code because I thought, this should never happen.
But you know what? The one thing I've learned in 30 years of consulting and building databases and stuff, is that you're going to run into these edge cases. Even when you think it might never happen, it's going to happen. Someone is going to do it. So you have to think of this weird stuff.
Did we need back compile? We should do it. All right. Let's go try it.
Let's add something to today. That's today's date and time. That's good. I tried these new Atlas bars. They're okay. They're kind of chalky. They're not bad. They're not as bad as some things, like the Quest bars, which are awful.
Now here we are in this date. If I added it, it should go into 4.001. Let's try it. Add an apple. Oh, there it is. Is that another one? See how it's going on the end though? That's the important part. If you look in the log, those should be, yep, same. 4.001. It's adding a second to each one so they stay in order. You could look at the food log. This works. This works.
We're not doing Myros probes here. This is going to come up again when we get to adding the meal items too, because you want them to stay in order. So we're going to add a second to them.
Another usability thing that I ran into when I was working with this, hit the plus button without something in the combo box and Invalidus and no, we could fix that easily. If the combo box is null, get out of town right in here. If it is null, food combo, then I use it sub. If you want to give them an error message, go right ahead. I don't care.
For me personally, this is my personal database, and I know that if I didn't put anything in there, it's not going to do anything now. I like to avoid those errors, especially if you're dealing with a database you're going to distribute to other people, because if you're giving them a compiled version, that error will basically crash the whole database.
Next up, I like when you're in here and you type in something like a P. It drops the box down while you're typing. But then, once you pick one, I want the box to not stay open there. Once I pick it, close the box. Unfortunately, there is no combo box.close method. There's only a drop down method. So we'll see how we deal with this in the Myros class.
So that's 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 tomorrow for part 38.
TOPICS:
Finding the most recent entry on a specific date
Using the DMax function to get the latest log entry
Handling null values with the NZ function
Adding a new item after the latest entry on a selected date
Using DateAdd to increment date time by one second
Avoiding date overflow when adding late entries
Manually adjusting date time to prevent data out of order
Testing by adding new items to the log
Handling null values in combo box before adding entries
COMMERCIAL:
In today's video, we're continuing with part 37 of our fitness database series. You'll learn how to figure out the most recent entry on a specific date and make sure new entries added to previous days appear at the end instead of the beginning. We'll cover using the DMax function to find the largest date-time value, adding a second to keep entries in order, and handling edge cases so nothing gets pushed to the wrong day. Plus, I'll show you how to stop errors from popping up if a combo box is left empty. 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 primary goal of the modification discussed in this video?
A. To ensure new entries are added to the top of each date's log
B. To guarantee that new entries for a specific date are added after the most recent entry on that date
C. To allow for deleting items from previous dates
D. To prevent duplicate entries in the food log
Q2. Which VBA function is used to find the latest date and time entry for a given date in the food log?
A. DLookup
B. DSum
C. DMax
D. DMin
Q3. What is the purpose of using the NZ function when assigning the result of DMax to a variable?
A. To convert the value to uppercase
B. To avoid errors if DMax returns Null by providing a default value
C. To make the result negative if Null
D. To force the result to be midnight
Q4. When adding a new food entry to a previous day, what modification is made to its timestamp to maintain chronological order?
A. One minute is added to the last entry's time
B. It is set to noon
C. One second is added to the largest timestamp on that date
D. It is set to midnight regardless of other entries
Q5. What edge case does the code handle when adding items to a day very close to midnight?
A. Deleting all entries at midnight
B. Entries might be added to the next day's log
C. Assigning identical timestamps to multiple items
D. Overwriting the first entry of the next day
Q6. How does the code correct itself if the newly calculated entry time moves into the following day?
A. It removes the entry
B. It resets all times to noon on the original day
C. It subtracts one second to bring the entry back to the correct date
D. It leaves the entry as is
Q7. When adding a new entry, what should happen if the combo box (food selection) is Null?
A. The code ignores the error
B. The code continues and adds a blank entry
C. The code exits the subroutine to prevent an error
D. A warning message is always shown
Q8. Why is it important to handle errors when distributing a compiled version of the database to other users?
A. To prevent unauthorized access
B. To avoid crashing the whole database due to unhandled errors
C. To enhance search performance
D. To allow unlimited data entry
Q9. Why does the instructor add one second rather than a larger unit of time to the timestamp when appending new entries?
A. To make sure entries are as close together as possible and maintain order
B. To reduce database size
C. To simplify the code logic
D. It is a random choice
Q10. What feature does the instructor wish Microsoft Access had, as discussed in the video?
A. A built-in spell checker for comments
B. Automatic error correction for SQL queries
C. Matching parenthesis or highlight for closing parentheses in the editor
D. The ability to play sound effects on errors
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-C; 7-C; 8-B; 9-A; 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 part 37 of my ongoing fitness database series. Even if you are not building a fitness tracker, this walkthrough is a great way to pick up some general tips and techniques for setting up databases in Microsoft Access. If you have not yet seen parts 1 through 36, make sure to check those out first so you are up to speed with everything we have accomplished so far.
The objective in this lesson is to figure out how to determine the most recent entry on a particular date. The issue I noticed is that when I add an item to a previous day, the new entry just gets tacked on at midnight. Instead, I want it placed at the end of the day's entries instead of the start, ensuring the order makes sense.
Let me walk you through the process I followed. I started by returning to the log function we previously set up, which handles adding food items to the log. This routine manages both new daily entries and ones that fall on different dates. If we are logging for today, the existing logic works fine. If you want to add to a previous date, though, we need to determine the maximum date and time for any entry already on that date.
We store this in a variable to hold the highest datetime for the day. To get this, I used the DMax function, which pulls the largest value for a field—in this case, the date and time of a food item—from the food log table, only for the entries that fall on our specified date. I made sure to account for the possibility that no entries exist on that day, so a fallback value of the date at midnight is assigned using the NZ function to handle any null results.
If you are not familiar with DMax, I recommend finding my dedicated DMax tutorial on my site, which will explain how it works in more detail.
Back to this routine—after finding the largest datetime for the date in question, the next step is to add one second to that timestamp for our new entry. This ensures the record goes just after the last one for that day, preserving order. If there are no records, the default will be midnight plus one second.
I also thought ahead to an edge case: what if the last entry on a day falls right at 11:59:59 PM? Adding one second would technically put our new entry just after the end of the day, rolling it into the next day. To prevent this, I put in a safeguard. If the new datetime now falls beyond our intended date, I reset it to just before midnight (11:59:59 PM) on that day. This handles the issue and keeps everything inside the day's boundaries.
These are little things, but over decades of building databases, you learn to expect users will eventually trigger even the rarest edge case. So it is good practice to add these safety checks in your code.
After writing and testing through Debug Compile, I added some new items for today to see how it works. Everything was functioning as intended. When I log entries for a past day, each one appears sequentially at the end, just as we want. This sort-order logic will also come up again as we build out functionality for managing meals, so it is worth getting right.
One more point about usability: I noticed that pressing the plus button to add an item without first picking something in the combo box produced an error. To fix this, I set up a simple check—if no item is selected, the routine exits without doing anything. This prevents unnecessary errors, which is especially important if you plan to distribute your database to others.
Lastly, I addressed a little annoyance. Normally, when you type in the combo box and the dropdown opens, it remains open after selecting an item. There is unfortunately no straightforward method in Access to close the box programmatically, just one to open it. We may revisit this in the future as we go deeper into the project classes.
That wraps up today's lesson. You can find a complete video tutorial with detailed, step-by-step instructions covering everything we discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Finding the most recent entry on a specific date
Using the DMax function to get the latest log entry
Handling null values with the NZ function
Adding a new item after the latest entry on a selected date
Using DateAdd to increment date time by one second
Avoiding date overflow when adding late entries
Manually adjusting date time to prevent data out of order
Testing by adding new items to the log
Handling null values in combo box before adding entriesArticle When managing a fitness tracking database, or any database that logs time-stamped entries, it is important to ensure that when you add a new entry for a past date, it appears at the end of that date's set of entries, rather than at the top. In Microsoft Access, by default, adding a new entry with just the date portion (no time specified) usually assigns it midnight, which can cause the new entry to appear before all other entries for that date. This can be confusing, especially if the order of the entries matters, such as when logging meals or workout activities.
To solve this, you want to determine the most recent (largest time) entry for a particular date. If you add a new item to a previous day, you want it to be one second after the latest entry for that date. If there are no entries for that date, then midnight is acceptable. Let me walk you through how you can accomplish this using VBA in Access, with some practical code examples you can adapt.
First, you'll use the DMax function. DMax finds the largest value in a set of records, much like the MAX aggregate in SQL. In this context, you want the largest DateTime value for a particular date in your log table. It's possible there may be no entries for the selected date, in which case DMax will return a Null—so you should use the NZ function to substitute a safe fallback value, like midnight for that day.
Here's how you could declare and calculate the wanted DateTime value in code:
Dim FDT As Date
FDT = Nz(DMax("food_date_time", "food_log", "food_date_time >= #" & DateValue(LogDate) & "# AND food_date_time < #" & DateValue(LogDate) + 1 & "#"), DateValue(LogDate))
This line looks for the latest food_date_time in the food_log table, for entries that are at least at the start of the selected date (midnight) and before the start of the next day (midnight next day). If no entry is found, it returns just midnight on the selected date.
Now you want to ensure your new entry is added after all existing entries on that date. To do this, simply add one second to the value you just calculated:
ProperLogDate = DateAdd("s", 1, FDT)
At this point, you have the correct timestamp for your new entry. However, there is a rare edge case you should be aware of. If your last entry is near the very end of the day, say at 11:59:59 PM, adding one more second will push your new entry to midnight of the next day—this is not what you want for an entry deliberately assigned to the previous date. To handle this, add a check:
If DateValue(ProperLogDate) > DateValue(LogDate) Then
ProperLogDate = DateValue(LogDate) + TimeSerial(23, 59, 59)
End If
This code compares the date part (ignoring the time) of the proposed new entry to the assigned date, and if it accidentally moves into the next day, it forces the time to the last possible moment of the correct date (11:59:59 PM).
You should also pay attention to usability details in your forms. For example, if your user tries to add a new log entry without selecting an item from a combo box, you should exit the subroutine or give an error message, to prevent invalid or blank entries from being created. Here is a typical approach:
If IsNull(Me.FoodCombo) Then Exit Sub
This small check will save you and your users from unnecessary errors. If the database is for your personal use, you might simply exit silently, but if you are distributing the database to others, it is wise to provide clear feedback or an error message.
By testing these changes, you can confirm that new entries for today (or any previous day) will always appear after the last entry for that date, preserving the correct chronological order. This method is also useful when you expand your log system to other areas, like grouping meal items or workouts, and you need to maintain data consistency and order.
In summary, by using the DMax and NZ functions to find the latest log entry for a given date and incrementing the time by one second, you ensure that things stay in the correct order. Handling edge cases and user errors with care helps keep your database robust and user-friendly. If you have not used DMax before, it is a very powerful tool for selecting the maximum value from a set of records matching conditions, and it is worth experimenting with in a variety of scenarios in your Access projects. |
