text to time conversion

[andybrst]

I've been banging my head against this problem for ages. regretably, in their wisdom, my company purchased software which records times as text without preceding zeros. ie. 9am is recorded as 900, whereas 10am is recorded as 1000.

Using format in the query properties I can show it as 09:00, but I need to concatenate to a date field to create a date/time stamp. When I have attempted this, the data refers back to 900.

When I have used TimeValue, data ending 00 comes back as an error and data recorded as like 1030 come back as 10:03!!

Help

Andy

 

[SydneyGeek]

Time is handled as fractions of a day. You'll need to calculate the number of seconds represented by your text values (eg: 900 is 9 hours, 00 minutes, or 9*3600 seconds). Divide this by 86400, the # of seconds in a day, and format the result as Date/Time.

Denis