Fitness 46
By Richard Rost
36 days ago
Why Timestamps Aren't Always What They Look Like
In this Microsoft Access tutorial I will show you how to handle duplicate timestamps in your fitness database's food log by creating a robust function to ensure each entry has a unique date and time to the second, explain the importance of removing milliseconds for accurate comparisons, and demonstrate changes to clean up existing code and prevent future issues with date and time values. This is part 46.
Members
There 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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, timestamps, SafeFoodDateTime, ProperLogDateTime, remove milliseconds, date time duplicate prevention, DateSerial, TimeSerial, DateAdd, DLookup, inequalities with date time, food log, meal log, add meal to log, loop counter, copy item time retention, before insert event
Subscribe to Fitness 46
Get notifications when this page is updated
Transcript
Got a good one for you today, folks. We are going to discuss timestamps and why they are not always what they appear to be. Even though you think you are adding a second, this can happen. Look at that. I will explain why a little bit later.
This is part 46 of my fitness database series. Whether or not you care about fitness, this is a database series about making cool databases, and I am teaching all kinds of cool tips and tricks, including today about timestamps. So let's get into it.
If you have not watched parts one through 45, I strongly recommend you go watch those first, but you do not have to. You should, but you do not have to.
In our food log, we have the ability to add a meal. Let's say I pick a meal and hit add, and it brings all these in at the exact time. Because it did now just switch from 5 o'clock to 5:01. If we look at the meal log, take a look down here at the bottom.
We have that code in there that adds a second when we are adding in from a meal. But I want something a little more robust that also works when we are adding just by copying items or by typing them in. So I do not necessarily like that loop counter that we built in one of the last couple classes.
Let's get rid of this here. What I want to do is go into the code and find where we have that loop counter thing. A loop counter thingy. It is a technical term. It is a loop counter thingy.
Let's add the log right here, and it is going to be add meal to log. Here it is with a counter, and we are sending into the add food item to log. We are sending in that counter as an offset for the number of seconds to add, which is a little cheesy. It works, but it is cheesy.
What I want to do instead, and I think a better, more robust solution that will also work in different places in the database too, is instead of this seconds offset, let's update proper and log date time so it checks for duplicates first. If there is a duplicate at that exact time index, add a second to it. We can get rid of all this seconds offset stuff.
Sometimes after I write some code, and you will find this out as a developer too, after I stew on it for a day or two and I think about it and it bothers me, my brain comes up with a better solution. This also gives me a method to teach you something really cool about date times in Access.
We are not going to do too much to change this guy. This guy works, but at the very end of this guy, we are essentially going to look and see if this date time already exists. If so, add a second to it.
I do not like to make one single function too complicated, so let's make a second function up here that does just that. We will send in a date time, and then we will look in the table to see if there is another exact record on that exact date time. We will call this Public Function SafeFoodDateTime. We will send in a date, and it will return a date.
Let's make this ByVal so we do not accidentally change the date coming in, because you might not want to do that.
We are going to look up a log ID item from the table. We will need a log ID. Or it is FoodLogID. Sorry, FoodLogID As Long. We will need a date to return. If the date to return is not valid, if there is already something else on that date time, we will increment it one second.
So, we will say DateToReturn As Date. For our loop, we will need to know if it is okay to exit the loop or not. We are going to keep looping, adding a second until we find a date time slot that is blank basically. Is there something at 9 o'clock and one second? Yes. Is there something at 9 o'clock and two seconds? Yes. Is there something at 9 o'clock and three seconds? No. We are okay to exit the loop.
We are going to start off DateToReturn as the date that is sent in. Start off by trying to see if the date you want to put it on is good. If it is, no work to do.
So, OKToExit = False. We are going to prime our loop. We are going to say While Not OKToExit because we have to do it at least once.
In the loop, what are we doing? We are going to look in the table and see if there is a record at that exact date time. So FoodLogID = Nz because if it returns a null, we want it to change it to a zero. DLookup, we are looking up a FoodLogID from the FoodLog table where the FoodDateTime equals whatever was sent in, which in this case right now is DateToReturn. Close up the date, close up the DLookup, comma zero.
Take the date we sent, which now becomes DateToReturn. So, we are going to look up in the table to see if there is another record with that exact date.
If FoodLogID = 0, that means it is not found. No record found, so OKToExit = True. Else, we are going to add a second to the DateToReturn. DateToReturn = DateAdd for this one, it is comma 1, comma DateToReturn. And then End If.
Is there something at one second? Yes. Loop. Is there something at two seconds? Yes. Loop. Is there something at three seconds? No. OKToExit is true. We come down here, and now we can say the name of our function equals DateToReturn just like that.
Save it. If you want to test this using the Immediate window, you can grab this guy, put a question mark, and then put something in here like 1/25/01, 01. You are going to get sub or function not defined because this has to be in a global module for the Immediate window to work with it.
This guy, as I wrote it, could fit in the global module. So I am just going to cut it out and paste it in there. So we will come down here, put it in the global module and drop it down here at the end. Then you can test it with that date, and it brings back that exact date.
Now, try testing it with something that is in the table. Let's find something that is in GoodLogT, and here is a good one. We will test with this date, that date type. Stick it in here, and then I will press enter. Look at that. It brought back one second after two o'clock, so we know it is working.
We can leave it here. It is not going to hit anybody in the global module. Save it. Give it a good Debug Compile. Now, if we go back to this guy, ProperLogDateTime, this guy just goes and makes sure that everything is good. If you are on a different date, does all that fixing. But at the very end of this one, we are going to say ProperLogDateTime = that function we just wrote, SafeFoodDateTime(ProperLogDateTime). So it will basically take the date that you now ended up with. Just check to make sure it is not already in the table. If it is, increment it by a second.
Now we can go to that AddItemToFoodLog and get rid of this seconds offset. This is no longer needed. I would not say dumb. It works, but it is kind of limited. We will have some problems with it later with some other stuff that I wanted. I discovered this when I was playing around with something else. For other things like copying items between days and things like that, we get some problems.
So we will get rid of that. We will get rid of the seconds offset stuff here. We do not need any of this, because now ProperLogDateTime just handles that.
Then in the AddMealToLog, which is, where is that? AddMealToLog. There you are. We can get rid of the counter out of here. We do not need this counter anymore. We can get rid of that. We can get rid of Counter = Counter + 1. We do not need a counter. We just add the items, and the function itself will automatically increment that seconds counter.
Let's give it a test. Save it. Debug Compile. Everybody compiles. Let's close it. Open it, and let's add that meal again. Ready, go.
Looks good. There are five new items. It is now 5:13. Let's take a look in the log table at the bottom. Oh, wait a minute. What is going on? What happened? 5:27, 5:28, 5:28, 5:28, 5:28, 5:28? What is going on here?
Well, the problem is, now does not just return that value to the seconds. Now is actually a floating point number. The exact value that you get is going to be 5:13:28.0326. If you actually look at what is in that value, Now returns milliseconds as well. It does not display it there, but they are in there.
What we have to do for this to work properly is chop off those milliseconds. I have gotten emails from people before. In fact, I think I did a video years ago. I think it is one of my developer lessons where a student was like, I am trying to do a comparison to see if there is another record at the same time. It says there isn't, but I can clearly see that there is. Yeah, because you cannot see the milliseconds there.
Access does not normally have an easy way to view those milliseconds. You can calculate it. You can figure it out. You can do what we are going to do, which is to isolate just the hours, minutes, and seconds, then subtract that from the value in the field. Then you can display the milliseconds. It is a pain in the butt.
If you want to see how to get milliseconds, let me know. For most business databases, you will never need it. But if you are doing comparisons or checking equalities to see if it is equal to this exact time, like we are doing, then you need to be able to do this.
So, we have to isolate that out and get rid of the milliseconds. Let's go back to our code, which is going to be in the global module. This guy. The first thing we are going to do is chop off the milliseconds from that value.
So, DateToReturn is going to be equal to - we are going to use DateSerial and build the date based on what is sent in. So it is going to be DateSerial. I have a whole separate video on DateSerial if you do not know what that is. There is DateSerial and TimeSerial. Here are the slides.
Here is DateSerial. You can put it together based on the year, month, and day. I do not have a separate TechHelp video for TimeSerial, but it works basically the same way, and I cover it in Access Expert Level 28.
DateSerial takes the year, which uses the Year function to separate the year out of a date, then the month, then the day. Then we are going to add to that, on the next line, the time over here, TimeSerial, which is the hour, the minute, and the second of the value.
What this is going to do is say, okay, what you are sending to me is a full date time to the millisecond, but I am going to chop that millisecond off by creating my own date value, which is this, this, this, this, and this. Then I am getting rid of the milliseconds.But if we're going to compare them, what's already in the table, compare them with what's already in the table, then we're no longer just checking for one value. We're checking for a range of values now. So we have to see if there's anything already in the table between that date and, well, actually greater than or equal to that date time, and less than one second in the future.
Because it could be nine o'clock on the dot, or it could be nine o'clock and 14 milliseconds. So we have to check for that. So now we need an inequality here.
So now we're looking up the value. The food date time can be greater than or equal to the date to return. And next line. Food date time is going to be less than some value. We'll put the value in a minute.
I do this a lot, by the way. I know I have to put some stuff in here, but I want to frame this first with the pound signs and the quotes and all that. Now that it's, and then click off it. Now that it's good, now that I can write what's going in here.
And I do need some more space. I'm going to back up a little bit. So what's going to be in here? Well, we've got to add one second to that. One clean actual second. We'll use date add. So date add S, one, date to return.
You see what I'm doing? Because we chopped it off up here. Now we have to see if anybody falls on that second in the table from zero milliseconds to 9999 milliseconds, less than one second in the future.
And then the rest of it's all the same thing. Let's give it a try. Save it. Debug compile. Close it. Close it. Open it. Let me get rid of this stuff. Delete, delete, delete, delete.
And let's add that meal again. You ready? Click. All right. We're all 5.09. And that's good. Let's check the log table. And there we go. I got that.
So now these are all clean values to the second. There's no millisecond fractional part on that second there. You can cleanly check it in the table to make sure that it works.
And now that we've updated that function, it will work anytime we use that add to log to add any item to the log. It'll always have clean seconds. That doesn't mean all the ones previously are clean, but anything moving forward will be clean.
That also means when we type stuff in, it'll be clean because our before insert event uses proper log date time. To get to this, it'll now be right to the second.
And if we use our copy item, let's see, copy item uses, oh, copy item's using now. Well, we're going to change this. In the next lesson, one of the things I want to do is when we copy items, like for example, here's the food log.
Let me get rid of this stuff. It's down here because I haven't eaten this. This is my actual data for today. So I'm trying to keep it clean. If we go to yesterday, for example, and I want to copy my yogurt and berries, it's at 11 o'clock. So we're going to go copy item.
Now, if I go back to today, it put it in it right now. What I want to change, and we're going to do this in the next class, is I want this to have the same time, update the date but keep yesterday's time, because the yogurt and berries I'm going to eat pretty much at the same time every day. Same thing with breakfast. And then we'll make some shortcuts for being able to update this stuff.
So that's it for today. Now, I also want to add one extra note. This is one of those situations where we're adding that time, looking up values. First, I did this to teach you something about date time values, but also, this is a solution that works fine in a small database like this.
Obviously, if we were dealing with, like I've used the Mars Rover example before, if we're doing NASA level programming, enterprise level stuff, where we're checking stock trades to the millisecond, there are better solutions you can use involving custom sort fields or complex indexing rules. You could set up a multi-field index here to prevent entries at the exact same time.
But there are lots of other solutions you can use. This is what I think is a good solution for this database. What's the phrase? It's good enough for government work. It works. It's a good solution. It's better today than it was yesterday. And I think it's a valid solution for what we're trying to do here. I'll stand by that.
So was that a cool trick? Did you learn something? Did you learn a little bit about the now function and how Access's timestamp will actually store milliseconds in it? That's why they're difficult to check to see if you have something else at exactly the same time, because you have to tear those milliseconds off of it.
That's why usually when it comes to working with dates and times in Access, I work with inequalities. Unless you're using just solid whole dates, use the date function. For most business things, date is fine. If you want to know when an order was placed, it was placed on the 15th.
Sometimes you want time. Sometimes you might want to know what time of day something came in. But if you're dealing with a little more precision, or if you're trying to do something like this, check out inequalities, and you have to do something like this.
All right, that's going to do it for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part 47, or do I?
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on. Did I mention it's completely free?
And if you like Level 1, Level 2 is just one dollar. That's it. It's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month, and yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus all of my beginner courses, all of them from every subject. You get one free advanced or developer class every month after finishing the expert series.
And you can become a diamond sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by accesslearningzone.com. I hope you enjoyed. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Identifying and solving timestamp duplication in Access
Explaining why adding seconds with a loop counter is flawed
Removing the seconds offset logic from meal logging
Creating the SafeFoodDateTime function to avoid duplicates
Using DLookup to check for existing datetime records
Incrementing time values by one second until unique
Understanding how Access stores milliseconds in DateTime
Chopping milliseconds off DateTime values using DateSerial and TimeSerial
Rewriting comparisons to account for potential milliseconds
Changing equality checks to inequality range checks for time
Integrating the new timestamp handling into existing logging code
Testing the new logic for adding and copying meal log items
Ensuring consistent DateTime values when adding or copying entries
Updating before-insert event logic to use precise DateTime handling
COMMERCIAL:
In today's video, we're continuing with part 46 of the fitness database series. We'll learn about how Access handles timestamps and why just adding a second is not always as simple as it seems. I will show you how to build a function that avoids duplicate timestamps in your logs by incrementing by one second only when needed, and how to remove those hidden milliseconds that can cause problems when looking for exact matches. You'll see how to streamline your code so all new log entries use precise to-the-second times, making your database cleaner and easier to manage. 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 main issue does the video address regarding handling timestamps in Access databases?
A. Timestamps sometimes repeat when adding multiple items at once, causing duplicates at the same exact second.
B. Timestamps always display milliseconds, making it easy to spot duplicates.
C. Timestamps are not supported in Access databases.
D. Timestamps are only stored to the nearest minute.
Q2. What was the initial approach used to ensure unique timestamps when adding multiple records?
A. Using a loop counter as a seconds offset for each item added.
B. Using the current date without any modification.
C. Combining item IDs to create unique stamps.
D. Relying solely on Access's auto-increment fields.
Q3. What is the main limitation of using a simple seconds offset loop counter approach for timestamps?
A. It only works when adding items via the specific function it is built into.
B. It makes timestamps non-unique.
C. It causes Access to crash on duplicate times.
D. It sorts items in descending order only.
Q4. What is the primary function introduced as a more robust solution for ensuring unique timestamps?
A. SafeFoodDateTime
B. UniqueTimestamp
C. CleanDateFunction
D. DateOffsetFixer
Q5. How does the new SafeFoodDateTime function work to ensure uniqueness?
A. It checks for an existing record at the target timestamp, and if found, increments the time by one second until a unique second is found.
B. It generates a random timestamp each time.
C. It deletes any conflicting records.
D. It uses the milliseconds for uniqueness.
Q6. Why is simply comparing two Access datetime values for equality sometimes unreliable when looking for duplicates?
A. Access datetimes can include hidden milliseconds that are not displayed.
B. Access only supports date, not time.
C. Access rounds up all times to the nearest hour.
D. Access ignores values with seconds.
Q7. What technique does the video show to strip milliseconds from a datetime?
A. By reconstructing the date using DateSerial and TimeSerial with year, month, day, hour, minute, and second only.
B. Multiplying the timestamp by 1000.
C. Using a custom format string to hide milliseconds.
D. Dividing the date value by 2.
Q8. After milliseconds are stripped, how are existing records checked for a match?
A. By checking for any record that has a datetime between the candidate time and less than one second later.
B. By exactly matching just the seconds portion of the timestamp.
C. By checking for dates within the same day.
D. By matching only the minute value.
Q9. What is the main benefit of using inequalities rather than strict equality when comparing timestamp values?
A. It allows the code to find records that might have hidden milliseconds and would otherwise be missed.
B. It speeds up query performance significantly.
C. It enables date-only comparisons.
D. It allows for duplicate timestamps.
Q10. Why might it still be necessary to perform more complex handling of timestamps in enterprise or high-precision databases?
A. Because very high activity or precision requirements demand sub-second or millisecond accuracy and stricter methods.
B. Because Access cannot store timestamps at all.
C. Because comparing timestamps by date only is too slow.
D. Because all records in Access are required to have unique times to the millisecond.
Q11. According to the video, after implementing the more robust timestamp function, what additional scenarios does it now handle without further modification?
A. Adding items by any method (typed in, copied, added by meal, etc.) will maintain unique, clean timestamps to the second.
B. Only items added via the AddMealToLog function will be checked for uniqueness.
C. Only new records created via copy will be checked for milliseconds.
D. It ignores all items added after the first add.
Q12. What lesson does the presenter emphasize regarding debugging and re-evaluating code solutions?
A. Often after thinking about or "stewing on" code for a day or two, developers come up with better solutions.
B. Code should never be revisited once it works.
C. Complexity should always be maximized for long-term benefit.
D. Once you write a function, you should never touch it again.
Q13. When is handling the milliseconds of a timestamp particularly important in business databases, as discussed in the video?
A. When precise comparison of two event times is needed, such as to avoid accidental duplicates.
B. When only the year of an event matters.
C. When dates are always entered manually.
D. When using only the date and not the time portion.
Q14. What should you check for in your timestamp solution if you want to maintain uniqueness as your methods for adding records expand?
A. Make sure your timestamp cleaning and duplication checking logic is used everywhere items can be added to the table.
B. Only check when items are added via one method.
C. Ignore timestamp uniqueness if using the Now function.
D. Only clean timestamps after data entry is complete.
Q15. What is the presenter's general advice for choosing technical solutions based on the level of project complexity?
A. Use an approach that is "good enough" for the current project's needs, and consider more complex solutions only as needed.
B. Always use the most complex and precise solution available.
C. Never consider future needs when picking solutions.
D. Only use built-in Access functions without modification.
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
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 covers an important topic in database design: working with timestamps in Microsoft Access, and more specifically, why timestamp values are not always as straightforward as they seem. If you have been following along with my fitness database series, this is part 46. However, even if you are not interested in the fitness side of things, the concepts we cover here apply to creating practical solutions for timestamp management in all kinds of databases.
Our current food log lets users add meals, and when you select a meal and add it, the individual food items are all assigned the exact same timestamp. Previously, we built a solution that simply added one second to each additional item using a loop counter, but that was somewhat limited. It only worked in specific scenarios, such as when adding a meal. I want a more robust system that works everywhere in the database: copying, typing, inserting, and so on.
Let me walk you through the issue with our old method. We were passing a simple counter to shift each timestamp by a second when adding items via our code. While this avoided duplicate timestamps, the technique was not reliable in more complex scenarios and would cause issues if we wanted more flexibility in the future, such as copying logged items or inserting records manually.
Today, I want to refine this by checking for duplicate timestamps directly in the table each time we try to add a new item. If a duplicate timestamp appears, we increment the timestamp by one second until we find a slot with no conflict. This way, it is handled universally across various entry methods.
To accomplish this, I am introducing a new function I call SafeFoodDateTime. When adding a food log entry, this function takes the proposed date and time and checks if another record with that exact timestamp already exists. If there is a conflict, the function adds a second and checks again, continuing until it finds an available time slot. This keeps our log entries each with a unique timestamp.
Before looping, I want to ensure our input date time is not changed accidentally, so I make sure the function takes its parameters by value rather than by reference. Inside the function, I retrieve the FoodLogID for the given date and time using a DLookup. If no record is found, we return the date as is. If a record is found, we add one second and repeat the process.
After testing the function, I realized that Access's Now function, which returns the current time, actually stores fractions of a second—milliseconds—within the timestamp, even if they are not displayed in most views. As a result, our checks for duplicates might not catch real duplicates if there is just a tiny difference in milliseconds.
This is a classic issue. In fact, many developers run into trouble when checking for equality on timestamps, as the milliseconds (which are not visible by default) cause false mismatches. For most business cases, you might not care about milliseconds, but if your code depends on unique timestamps to the exact second, you must address them.
The solution is to eliminate the milliseconds and standardize all timestamps to the nearest second. I use the DateSerial and TimeSerial functions to reconstruct the timestamp with only the year, month, day, hour, minute, and second values—effectively dropping anything more granular than a second.
However, when it comes to checking for existing timestamp conflicts in the database, I now need to look for any record with a time greater than or equal to the new timestamp and less than one second later. That way, I account for all records that may technically exist within that second, regardless of milliseconds.
Once I had that in place, I removed all the seconds-offset logic and any dependence on loop counters from my existing code. Whether adding meals, entering records manually, or copying records, this revised function guarantees that every log entry is spaced by at least a second, with no control characters or stray milliseconds left behind.
Testing the updated system, I verified that new food log entries are all recorded at a clean, to-the-second time with no hidden fractions of a second, regardless of how the items were added.
This improvement also means when typing in entries or copying items from one day to another, every entry receives a clean timestamp. For example, if you routinely eat the same breakfast at the same time each day, the next enhancement I plan is to allow copying the prior day's time when duplicating an item, so your log stays orderly.
I want to mention that, for a simple personal database like this, this method is more than sufficient. If you were designing a vast, enterprise-level system with high-frequency logging (like stock trade records at sub-second intervals or NASA data logging), you would need more advanced strategies such as composite indexes, custom logic for preventing duplicates, or possibly systems for handling fractional-second precision directly. But for typical Access databases, my solution is sound and practical.
I hope you found this lesson useful and perhaps picked up something new about how Access stores time values, especially the quirks regarding hidden milliseconds. This is why you should always be cautious comparing dates and times for exact matches. Usually, it's better to compare with inequalities instead of looking for perfect equality unless you are just dealing with whole dates.
That wraps it up for today's lesson. 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
Identifying and solving timestamp duplication in Access
Explaining why adding seconds with a loop counter is flawed
Removing the seconds offset logic from meal logging
Creating the SafeFoodDateTime function to avoid duplicates
Using DLookup to check for existing datetime records
Incrementing time values by one second until unique
Understanding how Access stores milliseconds in DateTime
Chopping milliseconds off DateTime values using DateSerial and TimeSerial
Rewriting comparisons to account for potential milliseconds
Changing equality checks to inequality range checks for time
Integrating the new timestamp handling into existing logging code
Testing the new logic for adding and copying meal log items
Ensuring consistent DateTime values when adding or copying entries
Updating before-insert event logic to use precise DateTime handling
Article
Today I want to teach you about handling timestamps in Microsoft Access, focusing on why comparing and storing time values is more complicated than it may seem — especially when you deal with seconds and milliseconds. Many database applications require stamping records with the exact date and time an event occurs, whether you are logging meals in a fitness application or tracking changes in a data record. At first glance, it might seem straightforward: just use the Now function every time you want to log an entry and assume each one is unique by at least a second. But Access actually stores timestamps with greater precision than you may expect, and this can cause subtle bugs when you want to ensure that every log entry gets a unique timestamp down to the second, with no overlap.
Suppose in your Access database you let users add multiple records at once — like logging several food items as part of a meal. If you use the Now function for each, the records might end up with the same visible timestamp (in hours, minutes, and seconds), but Access is actually storing the milliseconds as well. When you look in the table, several items may appear to share a timestamp, but internally, they're different because they have distinct milliseconds. This can break code that tries to look up or detect duplicate timestamps.
To illustrate, imagine you have an event where you add several items to your log at what appears to be the same time. The previous approach might have been to manually add a counter and increment the seconds for each entry, so items added in quick succession have a visibly different time. For example, adding yogurt at 9:00:00, toast at 9:00:01, and juice at 9:00:02. This works, but it's a bit of a kludge and not robust if users can also add or copy items at other points in the application.
A better, more reliable method is to check before inserting a new timestamp: is there already a record with this exact (seconds-only) timestamp? If so, increment the seconds by one and check again, continuing until you find a timestamp not already taken. This is what I call the "safe" timestamp approach, and it not only avoids collisions but also works consistently throughout your database regardless of how records are created or copied.
To implement this in Access VBA, first write a function called SafeFoodDateTime. This function accepts a date value, strips it to seconds precision (removing any milliseconds), and checks the log table for any record with an equal timestamp. If there is one, it increments the timestamp by a second and repeats the check. When it finds an unused second, it returns that value. Here is how you can write such a function in VBA:
Public Function SafeFoodDateTime(ByVal TestDate As Date) As Date
Dim FoodLogID As Long
Dim DateToReturn As Date
Dim OKToExit As Boolean
' Strip milliseconds from the input date
DateToReturn = DateSerial(Year(TestDate), Month(TestDate), Day(TestDate)) _
+ TimeSerial(Hour(TestDate), Minute(TestDate), Second(TestDate))
OKToExit = False
' Loop: check if there is already a record between this second and the next
While Not OKToExit
FoodLogID = Nz(DLookup("FoodLogID", "FoodLogT", _
"FoodDateTime >= #" & Format(DateToReturn, "yyyy-mm-dd hh:nn:ss") & "# AND " & _
"FoodDateTime < #" & Format(DateAdd("s", 1, DateToReturn), "yyyy-mm-dd hh:nn:ss") & "#"), 0)
If FoodLogID = 0 Then
OKToExit = True
Else
DateToReturn = DateAdd("s", 1, DateToReturn)
End If
Wend
SafeFoodDateTime = DateToReturn
End Function
Let's break down the process. The function receives a full Access Date/Time value, possibly including milliseconds. First, it uses DateSerial and TimeSerial to construct a new date/time to the precise second with milliseconds set to zero. This is necessary because Access, under the hood, stores fractions of seconds even if you do not see them in datasheets or forms. If you only compared hour:minute:second visually, two timestamps might appear identical but actually have slightly different milliseconds. As a result, code that tries to find duplicates could fail, believing that no collision exists, when in reality there are more precise values being compared.
Next, the function looks up in the FoodLogT table if any FoodLogID exists between that second and just before the next second begins. This ensures that even a 9:00:00.500 entry will count as being "within" 9:00:00 in the search. If no record is found in that one-second window, the function returns the cleaned timestamp. If a record is found, the function adds another second and repeats the check. This loop continues until a unique timestamp is found.
To test this function, you can call it from the Immediate window. For instance, enter:
? SafeFoodDateTime(#6/14/2024 09:00:00#)
If there is nothing logged at 9:00:00, it returns exactly that time. If there is already a record at that second, it returns 9:00:01, or increments further as needed.
Once this SafeFoodDateTime function is working, you can use it in any part of your code that assigns a log timestamp. For example, if you have a function called ProperLogDateTime that returns the timestamp for a record, make sure the last line of that function uses SafeFoodDateTime to finalize the value:
ProperLogDateTime = SafeFoodDateTime(ProperLogDateTime)
With this, any add-to-log routine, whether logging meals, individual foods, or copied items, can just call ProperLogDateTime and the function handles finding a unique timestamp.
This solution is especially important because if you use Now or similar functions to generate rapid timestamps, Access will still include milliseconds, increasing the potential for overlap and confusion. By explicitly limiting timestamps to whole seconds, you ensure that finding, displaying, or comparing record times will behave consistently for both users and your code.
While this solution is perfect for most business or personal database applications, be aware that truly high-precision systems, such as those tracking scientific data or financial transactions down to the millisecond or beyond, require more robust handling. That might include composite indexes, additional unique fields, or strictly synchronized time assignments, none of which are in scope for this sort of general-purpose usage. For Access and most small- to medium-scale applications, this approach is reliable and practical.
To sum up: when working with date and time fields in Access, always be aware of the invisible milliseconds stored in Date/Time values. When you need to ensure uniqueness by the second, strip out milliseconds before inserting or comparing, and use searching with inequalities to detect overlapping intervals, not just equal values. Using this SafeFoodDateTime approach will make your date comparisons robust and your logs tidy, preventing subtle bugs and making your data much easier to manage.
|
