Enhance VBA code to include Time in DateTime object when inserting new transaction

%3CLINGO-SUB%20id%3D%22lingo-sub-2093740%22%20slang%3D%22en-US%22%3EEnhance%20VBA%20code%20to%20include%20Time%20in%20DateTime%20object%20when%20inserting%20new%20transaction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2093740%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CBR%20%2F%3EI%20need%20to%20make%20some%20changes%20to%20a%20small%20legacy%20Access%20database%20to%20incorporate%20Time%20into%20several%20date%20objects%20used%20throughout%20the%20forms%20and%20reports.%20Currrently%20Time%20is%20not%20entered%20or%20stored%20anywhere%20within%20the%20database.%3C%2FP%3E%3CP%3EThe%20requirement%20is%20for%20the%20new%20transactions%20created%20to%20contain%20both%20date%20and%20time%2C%20with%20VBA%20code%20to%20handle%20all%20relevant%20determination%20of%20date%20and%20time%20values%20to%20be%20used%2C%20as%20well%20as%20creating%20the%20transaction%20records.%3C%2FP%3E%3CP%3ESituation%20is%3A%3CBR%20%2F%3E%26nbsp%3B%20TableA%20contains%20an%20object%3A%20round1_dt%20-%20data%20type%20date%2Ftime%2C%20format%20is%20not%20defined%3C%2FP%3E%3CP%3E%26nbsp%3B%20TableB%20contains%20an%20object%3A%20start_tm%20-%20data%20type%20date%2Ftime%2C%20format%20is%20Long%20Time%20(%20new%20table%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%20TableC%20contains%20an%20object%3A%20fixture_dt%2C%20-%20date%20type%20date%2Ftime%2C%20format%20is%20General%20Date%20(%20transaction%20)%3C%2FP%3E%3CP%3ECurrently%20TableA.round1_dt%20is%20assigned%20to%20TableC.fixture_dt%20by%20the%20insert%20in%20VBA.%3C%2FP%3E%3CP%3ENew%20requirement%20is%20for%20a%20Time%20value%2C%20from%20an%20applicable%20record%20in%20new%20TableB%2C%20to%20be%20used%20as%20the%20Time%20element%2C%20along%20with%20the%20Date%20element%20from%20TableA%2C%20in%20the%20value%20to%20be%20assigned%20to%20TableC.fixture_dt%20in%20the%20new%20transaction.%3C%2FP%3E%3CP%3EMy%20research%20on%20VBA%20hasn't%20provided%20me%20with%20any%20ideas%20or%20examples%20of%20how%20to%20achieve%20this%2C%20either%20to%20bring%20the%20two%20elements%20together%20at%20time%20of%20the%20insert%20or%20even%20updating%20the%20time%20element%20in%20the%20new%20transaction%20later%3C%2FP%3E%3CP%3EAccess%20VBA%20is%20not%20my%20forte%2C%20so%20any%20assistance%20would%20be%20appreciated%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2093740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2093965%22%20slang%3D%22en-US%22%3ERe%3A%20Enhance%20VBA%20code%20to%20include%20Time%20in%20DateTime%20object%20when%20inserting%20new%20transaction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2093965%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F941506%22%20target%3D%22_blank%22%3E%40MickK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20an%20interesting%20fact.%20ALL%20dates%20in%20ALL%20relational%20database%20applications%2C%20including%20those%20created%20with%20Access%2C%20contain%20both%20a%20date%20portion%20and%20a%20time%20portion.%20That's%20how%20they%20are%20stored%20internally%20in%20tables.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Faccess%2Fconcepts%2Fdate-time%2Fcalculate-elapsed-time%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EActually%2C%20the%20way%20they%20are%20stored%20is%20as%20a%20decimal.%3C%2FA%3E%20The%20portion%20of%20the%20date%20number%20to%20the%20left%20of%20the%20decimal%20point%20is%20the%20number%20of%20elapsed%20days%20since%20time%20began%20(well%2C%20since%20Access%20time%20started%2C%20which%20is%2012%2F30%2F1899).%20The%20portion%20of%20the%20date%20number%20to%20the%20right%20of%20the%20decimal%20point%20is%20the%20elapsed%20time%20since%20midnight%20of%20that%20day.%20To%20store%206%3A00%20AM%2C%20for%20example%2C%20you'd%20find%20that%20Access%20stores%20.25%20because%206%20hours%20is%201%2F4%20of%2024%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20is%20that%20important%3F%20Because%20it%20means%20your%20relational%20database%20application%20HAS%20BEEN%20STORING%20TIMES%20all%20along!!%20They%20just%20were%20stored%20as%2000%3A00AM%2C%20probably%2C%20because%20your%20application%20wasn't%20set%20up%20to%20capture%20the%20exact%20time%20and%20the%20only%20way%20to%20do%20it%20was%20to%20use%20that%20default%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20you%20do%20not%20see%20the%20time%20is%20FORMATTING.%20You%20can%20format%20any%20date%20(including%20the%20time%20value)%20to%20DISPLAY%20a%20huge%20variety%20of%20formats%20even%20though%20it's%20all%20the%20same%20thing%20internally.%20Check%20out%20this%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FfIrulXiP5KQ%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20YouTube%20video%20I%20created%20a%20while%20back%3C%2FA%3E.%20It's%20one%20of%20a%20series%20on%20this%20topic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20great%20thing%20about%20it%20is%20that%20you%20don't%20have%20to%20resort%20to%20VBA%20to%20get%20times.%20All%20you%20have%20to%20do%20is%20ensure%20that%20when%20you%20capture%20new%20date%20related%20values%2C%20you%20make%20sure%20you%20specify%20that%20the%20current%20time%20be%20used%2C%20not%20the%20default.%20And%20that%20you%20can%20do%20by%20using%20Now()%20instead%20of%20Date().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDate()%20returns%20the%20current%20date%20with%20the%20default%2000%3A00%3A00%20time%2C%20which%20is%20zero%20hours%2C%20zero%20minutes%2C%20zero%20seconds%20after%20midnight.%3C%2FP%3E%3CP%3ENow()%20includes%20the%20%3CSTRONG%3E%3CEM%3Ecurrent%3C%2FEM%3E%20%3C%2FSTRONG%3Eclock%20time%20(as%20a%20decimal%2C%20of%20course)%20instead%20of%20the%20default.%3CBR%20%2F%3EAnywhere%20in%20your%20VBA%20that%20manipulates%20dates%20can%20be%20adapted%20to%20ensure%20that%20you%20use%20Now()%20if%20you%20are%20just%20grabbing%20the%20current%20date%20and%20time.%20No%20fancy%20tricks%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2093969%22%20slang%3D%22en-US%22%3ERe%3A%20Enhance%20VBA%20code%20to%20include%20Time%20in%20DateTime%20object%20when%20inserting%20new%20transaction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2093969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20question%20you%20refer%20to%20getting%20a%20time%20from%20a%20second%20table%2C%20and%20that%20the%20time%20is%20FORMATTED%20as%20Long%20Time.%20That's%20one%20of%20those%20Format%20things%20which%20HIDES%20the%20fact%20that%20there%20is%20a%20full%20date%20and%20a%20full%20time%20actually%20stored.%20It's%20possible%20that%20FORMATTING%20it%20as%20a%20Long%20Time%20has%20stored%20a%20default%20date%20of%200%2C%20or%2012%2F30%2F1899%2C%20along%20with%20the%20time.%20If%20so%2C%20change%20that%20so%20that%20it%20captures%20the%20full%2C%20accurate%20date%20and%20just%20use%20it.%20Normally%2C%20you%20use%20Now()%20for%20that%20reason.%20It's%20also%20possible%20that%20you've%20been%20storing%20the%20actual%20dates%20all%20along%20and%20once%20you%20remove%20the%20formatting%2C%20you'll%20see%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2096999%22%20slang%3D%22en-US%22%3ERe%3A%20Enhance%20VBA%20code%20to%20include%20Time%20in%20DateTime%20object%20when%20inserting%20new%20transaction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2096999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20George%20thanks%20for%20the%20reply%3C%2FP%3E%3CP%3EI%20understand%20the%20mechanics%20of%20how%20dates%20and%20times%20are%20stored%20in%20Accesss%20db%2C%20and%20that%20the%20format%20controls%20how%20the%20values%20are%20displayed%20or%20not%2C%20and%20that%20all%20dates%20currently%20used%20in%20our%20db%20have%20a%20time%20set%20to%2000%3A00%3A00%3C%2FP%3E%3CP%3EI%20also%20understand%20concept%20of%20current%20date%20and%20time.%3C%2FP%3E%3CP%3ESome%20sample%20data%20may%20better%20explain%20my%20challenge.%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20TableA.round1_dt%20in%20active%20record%20contains%2025%2F02%2F2021%2000%3A00%3A00%20(%20dd%2Fmm%2Fyyy%20hh%3Amm%3Ass%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20TableB.start_tm%20in%20active%20record%20contains%2000%2F00%2F0000%2010%3A30%3A00%20AM%20(%20not%20sure%20of%20the%20date%20values%2C%20I%20assume%20the%20zeroes%20but%20it%20is%20the%20time%20that%20is%20important%20)%3C%2FP%3E%3CP%3EBased%20on%20some%20logic%2C%20it%20is%20determined%20that%20the%20new%20transaction%20is%20to%20be%20inserted%20with%20TableC.fixture_dt%20having%20date%20values%20from%20TableA.round1_dt%20and%20time%20values%20from%20TableB.start_tm%2C%20specifically%3CBR%20%2F%3E25%2F02%2F2021%2010%3A30%3A00%20AM%3C%2FP%3E%3CP%3Emy%20initial%20thought%20was%20to%20use%20DateValue%20to%20extract%20the%20date%20part%20from%20TableA.round1_dt%2C%20use%20TimeValue%20to%20extract%20the%20time%20part%20from%20for%20TableB.start_tm%20and%20then%20use%20these%20two%20text%20variables%20for%20the%20insert%20of%20fixture_dt%20into%20TableC.%3C%2FP%3E%3CP%3Eseems%20a%20bit%20unwieldy%2C%20and%20haven't%20actually%20coded%2Ftested%20it%20yet.%20looking%20for%20a%20simpler%20approach%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2097029%22%20slang%3D%22en-US%22%3ERe%3A%20Enhance%20VBA%20code%20to%20include%20Time%20in%20DateTime%20object%20when%20inserting%20new%20transaction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2097029%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F941506%22%20target%3D%22_blank%22%3E%40MickK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ejust%20realised%20I%20suggested%20DateValue%20and%20TimeValue%2C%20should%20have%20been%20DatePart%20to%20extract%20all%20elements%20to%20construct%20date%20and%20time%20values%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi
I need to make some changes to a small legacy Access database to incorporate Time into several date objects used throughout the forms and reports. Currrently Time is not entered or stored anywhere within the database.

The requirement is for the new transactions created to contain both date and time, with VBA code to handle all relevant determination of date and time values to be used, as well as creating the transaction records.

Situation is:
  TableA contains an object: round1_dt - data type date/time, format is not defined

  TableB contains an object: start_tm - data type date/time, format is Long Time ( new table )

  TableC contains an object: fixture_dt, - date type date/time, format is General Date ( transaction )

Currently TableA.round1_dt is assigned to TableC.fixture_dt by the insert in VBA.

New requirement is for a Time value, from an applicable record in new TableB, to be used as the Time element, along with the Date element from TableA, in the value to be assigned to TableC.fixture_dt in the new transaction.

My research on VBA hasn't provided me with any ideas or examples of how to achieve this, either to bring the two elements together at time of the insert or even updating the time element in the new transaction later

Access VBA is not my forte, so any assistance would be appreciated

thanks

8 Replies

@MickK 

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.

 

@George Hepworth 

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.

@George Hepworth 

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

@MickK 

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. 

@George Hepworth 

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.

@George Hepworth 

that did the job. 

Looking at your suggestion it makes sense when considering how the date and the time are stored in the number.

 

Thanks - appreciate you help on this.   

@MickK Congratulations on solving the problem.

Continued success with your project.