I have a subform/table with two fields. One is a URN (Sales EstimateID) and the other is set to date/time (TimeAllocation). I want to get the sum of all the records within the 'TimeAllocation' field for each 'Sales EstimateID' but I don't want to have a decimal figure returned. Can anyone help?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Time Calculation
- Thread starter OwenHall
- Start date
substitute
Technical User
Without trying it myself, does this work?
format(sum([timeallocation]),"d hh:mm"
format(sum([timeallocation]),"d hh:mm"
- Thread starter
- #3
This isn't quite hanging together. A "Date/Time" field contains a value like "05/22/2003 15:22:47" (i.e. date and time values). You say that you want a total which would mean that (if its really a date/time) that you want to add "05/22/2003 15:22:47" to "06/11/2003 09:27:41" for example. That doesn't have any useful result. If only the time portion (i.e. "hh:nn:ss" has meaning then the problem can be handled if we assume that the time so represented is hours, minutes and seconds allocated and not "wall-clock" time.
If the above rambling is correct then try
Format(cDate ( Sum
((Hours([TimeAllocation]) * 3600) +
(Minutes([TimeAllocation]) * 60 ) +
(Seconds([TimeAllocation])) / 86400), "hh:nn:ss" )
This
* Converts the hours, minutes & seconds to seconds and sums them
* Divides them by the number of seconds in a day (86400)
* Converts that to a date (cDate)
* Formats the date as "hh:nn:ss"
has meaning then the problem can be handled if we assume that the time so represented is hours, minutes and seconds allocated and not "wall-clock" time.If the above rambling is correct then try
Format(cDate ( Sum
((Hours([TimeAllocation]) * 3600) +
(Minutes([TimeAllocation]) * 60 ) +
(Seconds([TimeAllocation])) / 86400), "hh:nn:ss" )
This
* Converts the hours, minutes & seconds to seconds and sums them
* Divides them by the number of seconds in a day (86400)
* Converts that to a date (cDate)
* Formats the date as "hh:nn:ss"
substitute
Technical User
Date/time in Access is stored as a floating point number with the integer part representing the date as days since some arbitrary start and the fractional part representing the time as fractions of a day. So 12:00:00 is 0.5 and there is no need to perform calculations with 3600 and 86400
The reason I put sum() in my suggestion is because you want the sum of the timeallocation. I assume your first attempt put [timeallocation] in field row of the query grid with Sum in the total row. Instead you should now put my expression in the field row and Expression in the total row. Set up like that the sum should work.
As for the format, HH returns the sum modulo 24 hours which is why I tentatively added "D" to the format to mop up the whole days. It looks like that doesn't work properly in Access, so you need to remove the integer part of the sum and show it separately:
Expr1: Str(Int(Sum([time]))) & " " & Format(Sum([time]),"hh:nn:ss"
The reason I put sum() in my suggestion is because you want the sum of the timeallocation. I assume your first attempt put [timeallocation] in field row of the query grid with Sum in the total row. Instead you should now put my expression in the field row and Expression in the total row. Set up like that the sum should work.
As for the format, HH returns the sum modulo 24 hours which is why I tentatively added "D" to the format to mop up the whole days. It looks like that doesn't work properly in Access, so you need to remove the integer part of the sum and show it separately:
Expr1: Str(Int(Sum([time]))) & " " & Format(Sum([time]),"hh:nn:ss"
- Status
Similar threads
- Locked
- Question
