Adding up time in a Report

[KLewisBPM]

I have a multi user Access database with people typing in feedback of how much time they have spent on a particular job. Now when I try to create a report to give the total amount of time spent on one particular job I cannot get the time to add together and total at the bottom. I have tried various different ways and the closest I have come is where it is adding together but acting like a clock. If I have 23:00 hours and I add 2:00 hours instead of reading 25:00 it reads 01:00. I would appreciate any ideas of how to do this. Many thanks.

 

[JerryDennison]

Your values need to be numeric and not formatted as Date/Time. How are you getting the values you want to total?

 

[KLewisBPM]

The values Im getting are from a field formatted as time in a query. People book the time for a job in 15 min segments. 00:15, 00:30, 00:45, 01:00 etc. But I thought It would be possible to add time together so that you could get a total of hours and minutes at the bottom of your report.

 

[JerryDennison]

Not when it's formatted as time. If you think about it, your values really aren't time values (i.e. a specific hour/minute of the day) but are time intervals. You should treat them as numeric values.

 

[KLewisBPM]

Is there a way to convert the time into a numeric form and add it it to a second query (am I just wishing). If I cannot add time up when it is formatted as time. If I re format the field to a numeric value how will this effect the data that has already been inputed.

Thanks

 

[JerryDennison]

Formatting does not change the stored value. It depends on how you have your values stored. For example, if they are putting in 00:15 for 15 minutes of work then Access really sees this as 12:15am. So, to change this to a numeric value in minutes you would use the following syntax:

DateDiff("n", #00:00#, [TimeField])

This will give you the difference in minutes. You can find more options by looking up DateDiff() under help.

 

[KLewisBPM]

I have just found that the said field that holds the values 00:15 through to 12:00 in 15 minute segments is actually a lookup field. Therefore the field is formatted as just a number but the field it looks up to is formatted date/time.

Jerry, where would I type the syntax from above? thanks for your help.

 

[KLewisBPM]

I also have Crystal Reports 8.5 if it works and easier in that application. Would be worth doing instead?

 

[KLewisBPM]

One last question. (honest)

If I have a table with 4 columns. Column 1 = Auto number
Column 2 = Time (00:15 through to 12:00) column 3 = description and column 4 = new unused.

can I put some code in to convert what is in Column 2 (time)into a usable number i.e 00:15 is 15 minutes or 07:30 is 7 hours 15 minutes. I should imagine that it would work as long as the fact that 60 mins to an hour is taken into consideration.

Many thanks to anyone who can help me out on this.

 

[Guest_imported]

Yes, the code I gave you before will convert this "raw" time into hours. You can change the interval to minutes and return the number of minutes. Then use this value in your equations for adding the times and converting back into decimal hours or whatever format is most convenient for you.

 

[KLewisBPM]

I am at a loss I am not sure where I have to put the code suggested above. Is it in the query criteria field?

Many Thanks

 

[JerryDennison]

No, in an unbound field. Put it in an empty field in your query.