Time format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

larry - 22 May 2007 17:19 GMT

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 !

Signature

larry

Reply to this Message

Marshall Barton - 22 May 2007 18:32 GMT

>i am trying to change the format for one of the columns in my query.
>
[quoted text clipped - 4 lines]
>using field properties, what is the format that i can use to generate this
>result ?

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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Klatuu - 22 May 2007 21:31 GMT

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)

Signature

Dave Hargis, Microsoft Access MVP

> Hello,
>
[quoted text clipped - 8 lines]
>
> Thank you for your help !

Reply to this Message

Marshall Barton - 22 May 2007 22:34 GMT

>It appears your formatting is the total hours YTD + the minutes and seconds.
>If this is correct, then
[quoted text clipped - 6 lines]
> strWeirdString = (lngHours*24)+ hour(dtmWholeDate) & ":" &
>minute(dtmWholeDate) & ":" & second(x)

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")

Signature

Marsh
MVP [MS Access]

Reply to this Message

Klatuu - 23 May 2007 00:21 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> >It appears your formatting is the total hours YTD + the minutes and seconds.
> >If this is correct, then
[quoted text clipped - 17 lines]
> strWeirdString = 24 * lngDays + Hour(dtmWholeDate) &
> Format(dtmWholeDate, ":nn:ss")

Reply to this Message