Forum Discussion
Enhance VBA code to include Time in DateTime object when inserting new transaction
Here's an interesting fact. ALL dates in ALL relational database applications, including those created with Access, contain both a date portion and a time portion. That's how they are stored internally in tables.
Actually, the way they are stored is as a decimal. The portion of the date number to the left of the decimal point is the number of elapsed days since time began (well, since Access time started, which is 12/30/1899). The portion of the date number to the right of the decimal point is the elapsed time since midnight of that day. To store 6:00 AM, for example, you'd find that Access stores .25 because 6 hours is 1/4 of 24 hours.
Why is that important? Because it means your relational database application HAS BEEN STORING TIMES all along!! They just were stored as 00:00AM, probably, because your application wasn't set up to capture the exact time and the only way to do it was to use that default value.
The reason you do not see the time is FORMATTING. You can format any date (including the time value) to DISPLAY a huge variety of formats even though it's all the same thing internally. Check out this YouTube video I created a while back. It's one of a series on this topic.
The great thing about it is that you don't have to resort to VBA to get times. All you have to do is ensure that when you capture new date related values, you make sure you specify that the current time be used, not the default. And that you can do by using Now() instead of Date().
Date() returns the current date with the default 00:00:00 time, which is zero hours, zero minutes, zero seconds after midnight.
Now() includes the current clock time (as a decimal, of course) instead of the default.
Anywhere in your VBA that manipulates dates can be adapted to ensure that you use Now() if you are just grabbing the current date and time. No fancy tricks needed.
In your question you refer to getting a time from a second table, and that the time is FORMATTED as Long Time. That's one of those Format things which HIDES the fact that there is a full date and a full time actually stored. It's possible that FORMATTING it as a Long Time has stored a default date of 0, or 12/30/1899, along with the time. If so, change that so that it captures the full, accurate date and just use it. Normally, you use Now() for that reason. It's also possible that you've been storing the actual dates all along and once you remove the formatting, you'll see them.
Hi George thanks for the reply
I understand the mechanics of how dates and times are stored in Accesss db, and that the format controls how the values are displayed or not, and that all dates currently used in our db have a time set to 00:00:00
I also understand concept of current date and time.
Some sample data may better explain my challenge.
TableA.round1_dt in active record contains 25/02/2021 00:00:00 ( dd/mm/yyy hh:mm:ss )
TableB.start_tm in active record contains 00/00/0000 10:30:00 AM ( not sure of the date values, I assume the zeroes but it is the time that is important )
Based on some logic, it is determined that the new transaction is to be inserted with TableC.fixture_dt having date values from TableA.round1_dt and time values from TableB.start_tm, specifically
25/02/2021 10:30:00 AMmy initial thought was to use DateValue to extract the date part from TableA.round1_dt, use TimeValue to extract the time part from for TableB.start_tm and then use these two text variables for the insert of fixture_dt into TableC.
seems a bit unwieldy, and haven't actually coded/tested it yet. looking for a simpler approach
thanks
just realised I suggested DateValue and TimeValue, should have been DatePart to extract all elements to construct date and time values
MickK I see, and thanks for explaining that. I did sort of miss the point.
You will continue to have ONLY dates in one table and ONLY times in the other. And from those two individual fields, you'll have to construct a third value with date and time to place into a third field.
I think your plan to use DatePart to extract the relevant components and combine them is valid, as long as you can match up the two relevant records in the two source tables on some reliable join field in both tables.
