Well, I've read about all I can read in this forum, so I pose yet another elapsed time question:
I'm working with data that shows time as a number. Example 23:59:59 is displayed as 235959. I cannot change the format the the time data is, so everytime I run the query (make table query), it is stored as a 'number' format. When I change the input mask, it works for the current records, but when I run the query again, it's back to it's original format. Secondly, I need to calculate the elapsed time between a 'start time' and 'end time'.
I think I can get to the elapsed time part, if I could only get the initial data to stay in the correct format. Is there something I'm missing? All I need to do is convert the start time and end time into 00:00:00 format and keep it that way.
Thanks in advance. This is my last hope before I hang myself from a tree. I've been beating this thing around for about a week or so. Can't seem to get it.
I think you understand. I just don't understand where you place that function statement. The time data is currently in a number format 235959. I need to be able to format the data as 23:59:59, in two different fields (start time and end time), then calculate the difference for a final elapsed time. I was hoping to be able to do this with one query, but if not, I would be happy with just getting the data into the table with the proper format.
Is the statement above an expression in the query, or should I use it as criteria for the current time data fields already within the query?
This is the SQL query itself: SELECT Format([start_time],"00\:00\:00") AS s_time, Format([end_time],"00\:00\:00") AS e_time, DateDiff("s",[s_time], [e_time]) AS time_difference FROM your_table
The "Format([start_time],"00\:00\:00") AS s_time" just converts your number into the time format that you want and then aliases the field as s_time. I then do the same for the end time. Next I use the DateDiff function to calculate the number of seconds between the two times. You might have to do some additional formatting depending how you want the result displayed.