Time format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

i am trying to change the format for one of the columns in my query.

basically, i would like:
"1/6/1900 4:46:25 PM" to read as "160:46:25"
"1/2/1900 3:43:31 AM" to read as "51:43:31"


using field properties, what is the format that i can use to generate this
result ?

Thank you for your help !
 
larry said:
i am trying to change the format for one of the columns in my query.

basically, i would like:
"1/6/1900 4:46:25 PM" to read as "160:46:25"
"1/2/1900 3:43:31 AM" to read as "51:43:31"


using field properties, what is the format that i can use to generate this
result ?
Click to expand...


Can you explain how you get from a full date/time to what
you want? It doesn't look like a formatting issue, more
like it's a calculation of some kind.
 
It appears your formatting is the total hours YTD + the minutes and seconds.
If this is correct, then

dtmWholeDate = #1/6/1900 4:46:25 PM#
dtmDateOnly = DateSerial(Year(dtmWholeDate), Month(dtmWholeDate),
Day(dtmWholeDate)
lngHours = round((((dtmDateOnly /365.255) - (year(dtmDateOnly) - 1900))
* 365.255)-1) + Hour
strWeirdString = (lngHours*24)+ hour(dtmWholeDate) & ":" &
minute(dtmWholeDate) & ":" & second(x)
 
Klatuu said:
It appears your formatting is the total hours YTD + the minutes and seconds.
If this is correct, then

dtmWholeDate = #1/6/1900 4:46:25 PM#
dtmDateOnly = DateSerial(Year(dtmWholeDate), Month(dtmWholeDate),
Day(dtmWholeDate)
lngHours = round((((dtmDateOnly /365.255) - (year(dtmDateOnly) - 1900))
* 365.255)-1) + Hour
strWeirdString = (lngHours*24)+ hour(dtmWholeDate) & ":" &
minute(dtmWholeDate) & ":" & second(x)
Click to expand...


Excellent deduction, Dave. I doubt I would have have come
up with hours since base date.

Once you pointed that out and because I feel uncomfortable
using a value like 365.255, I came up with somewhat
different code:

lngDays = DateDiff("d", #12/31/1899#, dtmWholeDate)
strWeirdString = 24 * lngDays + Hour(dtmWholeDate) &
Format(dtmWholeDate, ":nn:ss")
 
I am not really comfortable with that calculation, either. I did not come up
with it. Believe or not, I got it off MSDN. (and we know how accurate it
always is ).
I like your way better. It is one of those where you go Doh! that is to
obvious. I should have thought of it.
 
Back
Top