Convert Numbers to Hours in MS Access

First, back up your database. Then, in the table, change the current "time" field from number to text. Save the table. Create an update query using the field that contains the "time" value. In the Update row, type the following, or copy (CTRL-C) and paste (CTRL-V)  (because of this board's width, I've formatted the line xxsmall) :

Format(Left(Format(CStr([ctime]),"0000"),2) & ":" & Right(Format(CStr([ctime]),"0000"),2),"Medium Time")

Substitute your field name for the [ctime] used above.

Run the update query. Go back to the table and change the data type in your "time" field from Text to Date/Time, format as Medium Time.

Message Edited by abach on 10-20-2004 09:19 AM

Allan,

You da Man! Thanks so much for your help. 

(I've really got to quit relying on the built-in functions, and learn more VB!)

You can do much more by knowing what functions are available. The Cstr function is really not necessary in the Update row. I placed it there in case you were unable to convert the field to a Text data type. Cstr converts a value to a string (text).

I used the Format function to convert any three character entries to four characters. Then, the Left and Right functions pulled the two left and two right characters, then concatenated (joined) them with a colon to create a time format like hh:mm.

An excellent site for coding is www.mvps.org/access as it has many good ideas.

If you have any other questions, let me know.

Message Edited by abach on 10-20-2004 02:07 PM