short time to a number format

[cheryl27284]

I'm working with a date/time field in which I need the military time ( short time) to display, however once the time is in the " short time" data type I need for it to be changed to a number data type without loosing the format. For example if the " start_time" is 13:00 I need to change the 13:00 to the number 1300.

 

[sfreeman]

Assuming that your military time will always be in the format of hh:mm a simple function can do the conversion.

Public Function ConvTime(datInputTime) As String
Dim HR As String
Dim Min As String

datInputTime = Format(datInputTime, "hh:mm")
HR = Left(datInputTime, 2)
Min = Right(datInputTime, 2)

ConvTime = HR & Min

End Function

Then in your query, update the text field like this:

UPDATE Tbl1 SET Tbl1.[NumberText] = ConvTime([DateSpec]);

Should change any military time from 00:45 to 0045, etc.

 

[MichaelRed]

To generalize the concept, consider visiting the "Split" function with the colon (":") character as the seperator. After mastering Split, look up Join to re-concatenate the value set(s) as desired.

On the otherhand, WHY in the world would anyone consider doing this? What is sooooooooooooooo wrong with the conventional use of time format?

(just curious).






MichaelRed
mlred@verizon.net

 

[PHV]

And what about something like this ?
SELECT Format([start_time],"hh:nn") As MilitaryTime, CInt(Format([start_time],"hhnn")) As NumberTime

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244