over 24 hours

[Guest]

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

 

[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

 

[Guest]

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.

 

[Aaron LIVES!]

it's not called a timeStamp retard

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

 

[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.

 

[Guest]

Okay.

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

thx

 

[Douglas J. Steele]

Correct.

 

[Guest]

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.

 

[Guest]

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

 

[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

 

[Guest]

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.

 

[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))

 

[Guest]

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!

 

[Douglas J. Steele]

Yes, once you've got a function defined, you can simply refer to it in your
queries.

Jeff Conrad's got a good list of references to good books at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Books

 

[Guest]

I will utilize this resourse... again, thank you.