Convert decimal to time.

[turks67 via AccessMonster.com]

Is it possible to convert 6.35 to 6:35 and be able to sort that column by
time order?

Thanks

 

[Piet Linden]

Is it possible to convert 6.35 to 6:35 and be able to sort that column by
time order?

Thanks

You would have to treat it as text and then convert. Access does not
store dates as strings. It stores them as numbers. The integer is
the number of days from something like 1/1/1904 or something equally
seemingly random. The decimal is the time of day. So you could use
LEFT/RIGHT/INSTR to get the hours and minutes and combine them
correctly.

 

[Marshall Barton]

Is it possible to convert 6.35 to 6:35 and be able to sort that column by
time order?

You can sort numbers like 6.35 just as well as times like
6:35 so that is not a reason to convert them. If the time
field is really a text string, then just sort on an
expression like Val(tf)

If you have some other reason to convert that kind of
number, then use an expression like:
Int(tf) / 24 + (tf - Int(tf)) * 100 / (24*60)
or if your time field is a text field:
TimeValue(Replace(tf, ",", ":"))

 

[turks67 via AccessMonster.com]

Thank you Marshall Barton. Now I have to find out how to add AM and PM.

 

[John W. Vinson]

How are AM and PM depicted now? If 6:00 pm is depicted as 18.00, Marshall's
solution will work with no problems (and you can use the Format property of
the resulting date/time field to display what was 18.00 as 6:00 PM).

 

[John Spencer]

One more option:

Int(16.35) & Format(Int((16.35 - Int(16.35))*100),"\:00")
Will return a string 16:35

You can then change that to a time with the TimeValue or CDate. The
original value must never be NULL or you will get an error when you try
to convert to an datetime value.

CDate(Int(16.05) & Format(Int((16.05 - Int(16.05))*100),"\:00"))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================