over 24 hours

T

tryn''''2learn

Hello,

I'm trying to get total hours when it exceeds 24hrs (working in queries)
in excel it is working fine but when i pull it over to access i'm getting
different data:
excel -
F1 F2 F3 F4 F5 total time
23:19:24 3:59:46 2:39:56 3:59:46 23:19:24 57:18:16
1:14:20 7:07:07 0:24:59 7:07:07 1:14:20 17:07:53

access:
F1 F2 F3 F4 F5 total time
23:19:24 3:59:46 2:39:56 3:59:46 23:19:24 9:18:16
1:14:20 7:07:07 0:24:59 7:07:07 1:14:20 17:07:53

I appreciate your help
 
D

Douglas J. Steele

The Date/Time data type is intended to be used for timestamps (i.e.:
specific date/time points in time). To store durations, you really should
use a long integer that represents whatever granularity you require (second,
minutes, etc.)

For a kludge, see my October, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html
 
T

tryn2learn

I see the help, but I must confess... I don't know how to use it.
how would I apply this to my sheet.

please forgive my lack of knowlege in this, I'm still very new to access.
 
A

Aaron LIVES!

it's not called a timeStamp retard



Douglas J. Steele said:
The Date/Time data type is intended to be used for timestamps (i.e.:
specific date/time points in time). To store durations, you really should
use a long integer that represents whatever granularity you require (second,
minutes, etc.)

For a kludge, see my October, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html
 
D

Douglas J. Steele

You'll have to create a function, and use that function to display the value
that's stored in the field in question.
 
T

tryn2learn

Okay.

So this function is placed in the modules area then it can be called up in
queries and reports. is this correct?

thx
 
T

tryn2learn

That you very much for your help and patience with me... i will be sure to
update of my progress via this posting.

again thank you.
 
T

tryn2learn

I've taken the function from your listed site and created a module called
TimeConv
my problem now is i've been unable (I haven't figured out how) to call it up
in my query. how do I call this function up in the Query
 
D

Douglas J. Steele

It's the ReformatTime function you want. You pass it a date field that
contains a time duration.

SELECT F1, F2, F3, F4, F5, ReformatTime(F1 + F2 + F3 + F4 + F5) AS TotalTime
FROM MyTable
 
T

tryn2learn

ok. I'm glad I at least grabbed the correct function. i pasted your below
expression in a new field window but got the error:

The syntax of the subquery in this expression is incorrect.
Check the subquery's and enclose the subquery in parentheses.

i again appologize for my lack of knowlege in this, but i sincerly
appreciate your help.
 
D

Douglas J. Steele

What I showed was the SQL of your query. It's not something you'd type into
a field.

If you're building the query through the graphical building, you'd type:

TotalTime: ReformatTime(F1 + F2 + F3 + F4 + F5)

into a blank column.

BTW, if any of F1, F2, F3, F4 or F5 can be Null, you'll run into problems.

You may want to use:

TotalTime: ReformatTime(Nz(F1, 0) + Nz(F2, 0) + Nz(F3, 0) + Nz(F4, 0) +
Nz(F5, 0))
 
T

tryn2learn

You are the man!

thank you so much that worked perfectly... so that i understand when you
have a working funtion all you have to do is name it the build as you've done
below? wow, again thank you.

what would you suggest i study to further my knowlege in access and the
coding/VBA/SQL?

again thank you!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top