Totalling Time fields

[Fozi]

Hi folks.

In a continuous subform users are entering their start times and end times per day. I have a control called [Total Hrs] that calculates the number of hours and minutes worked per day.

What I want now is a control that will total all five days entries to give me a total for the week. Tried =Sum([Total Hrs]) but to no avail.

All help greatly appreciated.

Fozi

 

[JANR]

Try putting the control in the subform footer.

JR

Edit: and put another control on your parentform and set it's controlsource to your control in the subform

 

[Fozi]

Thanks for the response JR but that's where it is at present.

Fozi

 

[JANR]

Did you se my edit on the last post?

 

[Fozi]

Tried that but to no avail i'm afraid. The control which has the formulae =Sum([Total Hrs]) is called [totall] but returns a zero value. So even when a new control set in the parent form uses it as it's control source it returns zero also.

 

[Dennisk]

Just be aware that totaling hours and minutes from a datatime field will only go up to 23h 59 min. This is the way Access works (or at least the versions I have worked on)

You may have to spilt the hours from the mins and perform
int(totalmin/60) to get the hours and mod 60 on the total minutes to get the remainder.

 

[missinglinq]

You cannot use functions such as SUM against calculated fields, such as your [Total Hrs] field! You have to use it against the calculation itself.

Presumably you're using something like

[Totatl Hrs] = EndTime - StartTime.

Rather than using

=Sum([Total Hrs])

you'd have to use

=Sum(EndTime - StartTime)

 

[Fozi]

Hey folks

Still struggling with this I'm afraid. Would any of you mind if I uploaded the an example here for to look at and advise?

Simply put users enter their times for each day. A calculated field works out their hours and minutes per day. I'm then trying to create a field in that form footer which sums their daily totals for a total hours and minutes per week.

Thanks for any assistance offered.

 

[DCrake]

Please view amendments to mdb for issues raised.

 

[Fozi]

Thanks DCrake

Not the first time you've bailed me out! You're a genius man.

 

[Fozi]

Hi DCrake

I'm working on the help you offered recently and have hit a snag. Have inserted a couple of calculated controls which capture what individuals are due to work each week against what they've actually worked to identify the difference.

Finding that when it is in deficit the minstotime function returns an incorrect value. Of course it's always possible and more probable that I've missed something. Any chance you coould have a look at the attachment?

Cheers
Fozi

 

[DCrake]

See changes made to form

 

[Fozi]

Thanks DCrake.

Ideally I'd like it to show positive and negative values so as to perform the calculation of time owed or time taken each week.

Is this possible without affecting the time module? Otherwise I guess I can run an if statement reducing the total if time worked is less than time due.

Thanks again
Fozi

 

[DCrake]

As calculations are not generally stored in a table and are generated in queries you can apply your formatting and summing there.

You could adjust the iif() on the form to show the relative calculation