Totalling Time Values

[l3111]

Hi AccessForums.net!!!

I'm having a major problem trying to sum up total time spent with customers. At presetn in my database i have 8 fields entiled ActualWeek1, ActualWeek2, ActualWeek3 etc all the way up to ActualWeek8.

The admin staff are entering in to these fields the time spent with customers. However, i need to total up the time spent, and some times are being entered as a decimal and some as hh:mm. EG if a customer has 8hours 45 mins, some staff are entering 8.75. but if a customer has 12 hours 55 mins, staf are entering 12.55 as its very difficult to work out this as decimal

I need to add up total time as hh:mm but if it goes over 24:00, access won't show it!!

how do i fix this?

staff will enter in hh:mm, not decimals so if you add 1.55 to 1.55 you get 3.10 but as this is time (1 hour 55 mins + 1 hour 55 mins) it should equal 3 hours 50 mins


So in summary
how do i sum times over 24 hours
how do i enter times in hh:mm format
and how do ensure that i don't have to use decimals

many thanks



l3111, Manchester, UK

[June7]

Several ways you can deal with this. All probably involve educating users on correct entry format.

You could try an input mask for hours:minutes for a single textbox entry.

You could have users enter the hours and minutes into separate textboxes

and either save them separately in table and concatenate them when need or

use code to save as concatenated value into one field.

If value is saved as hours and minutes, will have to do the math to convert minutes to decimal hour in order to do sum then reverse the calc on the sum to get the hours:minutes structure.

Or require users to enter hours and decimal hour. Show a conversion chart on the form if the arithmetic is too much for them.

[rpeare]

In your table set the format of your time fields to SHORT TIME
in the table also set the INPUT MASK to 00:00;0;_ (you can also choose this from the list if you click the button with the ... mark on it)

If you have already created forms you will need to go to each form and set the input mask of those too.

In your query that totals hours (or if it's on the form it's the same thing)

Use this:

TotalHours: DatePart("h",[actualweek1])+DatePart("h",[actualweek2])+DatePart("h",[actualweek3])+DatePart("h",[actualweek4])+Int((DatePart("n",[actualweek1])+DatePart("n",[actualweek2])+DatePart("n",[actualweek3])+DatePart("n",[actualweek4]))/60)+((DatePart("n",[actualweek1])+DatePart("n",[actualweek2])+DatePart("n",[actualweek3])+DatePart("n",[actualweek4])) Mod 60)/60

To break it down:

This part:

TotalHours: DatePart("h",[actualweek1])+DatePart("h",[actualweek2])+DatePart("h",[actualweek3])+DatePart("h",[actualweek4])+

Totals all the hour parts

This part:

Int((DatePart("n",[actualweek1])+DatePart("n",[actualweek2])+DatePart("n",[actualweek3])+DatePart("n",[actualweek4]))/60)+

Totals all the minutes and takes the integer value (does not round up) of the total minutes divided by 60 (or additional hours)

This part:

((DatePart("n",[actualweek1])+DatePart("n",[actualweek2])+DatePart("n",[actualweek3])+DatePart("n",[actualweek4])) Mod 60)/60

Takes the remaining minutes after you divide by 60 and divides that number by 60 to get the fraction of an hour.

I only did this formula for 4 fields (actualweek1 through actualweek4) but it should be easy enough to add the addionalweek fields to the forumla. Just a note, if your SQL statement gets too long (and it may with this many fields) just shorten your field names to AW1, AW2, AW3 etc. and you should have more than enough space for the forumla to work.