ODBC Query
Posted: Thu Sep 22, 2011 1:28 pm
I have two fields in my database, TRANS_DATE is a date, and TRANS_TIME is 100's of seconds since midnight.
The following Query combines the two fields into a single Date/Time field in MS-Access.
Date: CDate(Format([TRANS_DATE],"Short Date") & " " & Format(Int([TRANS_TIME]/360000),"00") & ":" & Format(Int([Trans_time]-Int([TRANS_TIME]/360000)*360000)/6000,"00") & ":" & Format([Trans_time]/100 Mod 60,"00"))
This example works with Finanan, and Time is stored in our “100 of Seconds since midnight Format”.
The formula converts Date & Time to Text, and then CDATE combines the two Into Date/Time.
The following Query combines the two fields into a single Date/Time field in MS-Access.
Date: CDate(Format([TRANS_DATE],"Short Date") & " " & Format(Int([TRANS_TIME]/360000),"00") & ":" & Format(Int([Trans_time]-Int([TRANS_TIME]/360000)*360000)/6000,"00") & ":" & Format([Trans_time]/100 Mod 60,"00"))
This example works with Finanan, and Time is stored in our “100 of Seconds since midnight Format”.
The formula converts Date & Time to Text, and then CDATE combines the two Into Date/Time.