Time/Hours in a Table

[georgereni]

georgereni (IS/IT--Manageme) Jun 21, 2004
to all who can help:

I am exporting a report from our internal reporting server that has information in a time format for each employee.

Example:

LastName, FirstName, Login Time
Smith John 3305:31:40

Which means that John Smith has been logged in for Three Thousand five hours, thirty one minutes and forty seconds.

When I import the data into an access table, it records the time as date and time, and messes the numbers up.

it will show that John Smith's login time as: 4/19/1900 5:00pm, or when I change the setting in the design view to short time it will change the record to 4/19/1900 2:40 am.

How do I get the table to show that the data is hours or total mins calculated and not time?

 

[PHV]

In a query try something like this:
TotalTime: (24*Int([Login Time])+Format([Login Time],'h')) & Format([Login Time],':nn:ss')

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

 

[SkipVought]

Hi,

You are correct; Time is part of date.

When you enter 3305:31:40, Access takes the STRING "3305:31:40" and does a conversion to Date/Time by converting 3305 minutes to Days and Minutes.. Hence it displays 4/19/1900 5:00pm which is 137 days (actually 136 days AFTER 1/1/1900).

If you don't want a Date displayed, you'll have to break the time into Hours, Minutes & Seconds columns.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[georgereni]

Is there any way that will allow me to convert
0:00:57 into seconds in access?

 

[PHV]

Have you tried this ?
CDbl(YourTimeValue) * 86400

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

 

[georgereni]

Where would I enter the expression?

 

[PHV]

Where would I enter the expression?
How can I know ? Here all the infos you gave us:
Is there any way that will allow me to convert
0:00:57 into seconds in access?

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