General discussion


access 2002 and calculating time fields

By shawnj985 ·
I have created a access database whose table consists of records with a overtime data field containing hours and minutes (HH:MM). I would like to create a total field at the end of the report that will give me the total over time for all records.

How can I do that within the report? Is it easier to use just one field like I have above or use two fields suchs as HH and MM then perform the calculations?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Have you stored it as a string ?

by Tony Hopkinson In reply to access 2002 and calculati ...

I wouls have stored it as an integer number of minutes and then converted on output as probably the easiest way. It's far easier to get SQL to do arithmetic on integers and then convert as the last step. Quicker too.

Collapse -

RE: Have you stored it as a string ?

by shawnj985 In reply to Have you stored it as a s ...

No. its not stored as an integer but I can.

But how do I perform the calculations on the hours and minutes field? keeping in mind that 60 minutes = an hour.

Collapse -

It does ?

by Tony Hopkinson In reply to RE: Have you stored it as ...

No you don't want to store it as a string and as far as SQL is concerned storing it as a datetime makes it more difficult as well if you want to sum or average.

Don't take the syntax as guaranteed by the way I don't use access and it's sql compliance is a tad spotty on occasion. Come to think of it so is the SQL off the top of my head.

For one value
chaining queries together, you could use an inner query or a temporary table in a proper database.

Select MyIdentifier, overtimeinminutes / 60 as OVERHOURS, overtimeinminutes - ((overtimeinminutes / 60) * 60) AS OVERMINUTES From OvertimeTable
Where ....

Select MyIdentifier,
Convert(VarChar(5), Replicate('0',2 - DataLength(ConvertVarChar(2),OVERHOURS)) + Convert(VarChar(2),OVERHOURS) + ':' + Replicate('0',2 - Datalength(Convert(varchar(2),OVERMINUTES)) + Convert(Varchar(2),OVERMINUTES)
from overquery

or something like anyway.
Note assumes you'll never go over 99:60
and that you didn't ant need to to do Days hours and minutes, that I'll leave as an exercise for the class.

Replicate returns a string of the specifed character of length n

Datalength returns the width of a variable/field, note datalength of an integer will be 4, datalength of a char(2) will always be 2 !
Varchar its equivalent to length though.

Related Discussions

Related Forums