Forum Discussion
Enhance VBA code to include Time in DateTime object when inserting new transaction
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 AM
my 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.
I would probably do this in an update query, rather than VBA, though.
UPDATE TableC SET TableC.start_tm = DateValue(TableA.round1_dt) + TimeValue( TableB.start_tm)
FROM TableA INNER JOIN ( TableB INNER JOIN TableC On TableB.YourJoinField = TableC.YourJoinField) on TableA.YourJoinField = TableB.YourJoinField
That assumes, of course, that there is a common field in all three tables which can be joined in the query. Also, that I managed to work out the proper nesting of Joins. Access is quirky.