Summing up total time in a contious form

  • Thread starter Thread starter JOM
  • Start date Start date
J

JOM

I have a subform that has activityDate, startTime and EndTime. I would like
to display to the user the total time an activity took per row and then a
grand total on the main form
Activitydate StartTime EndTime TotalActivityTime
01/02/2009 8:00 AM 8:30 AM 30 min
01/03/2009 9:00 AM 9:15 AM 15 Min
01/03/2009 9:25 AM 9:55 AM 30 min

GrandTotalActivityTime 1 hour 15 min

TotalActivityTime and GrandTotalActivityTime are unbound
 
On Wed, 21 Oct 2009 21:45:01 -0700, JOM

On the main form you could have a textbox with a ControlSource of:
=Sum(Forms!MyForm!MySubformControl.Form!TotalActivityTime
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP
 
I have a subform that has activityDate, startTime and EndTime. I would like
to display to the user the total time an activity took per row and then a
grand total on the main form
Activitydate StartTime EndTime TotalActivityTime
01/02/2009 8:00 AM 8:30 AM 30 min
01/03/2009 9:00 AM 9:15 AM 15 Min
01/03/2009 9:25 AM 9:55 AM 30 min

GrandTotalActivityTime 1 hour 15 min

TotalActivityTime and GrandTotalActivityTime are unbound
Click to expand...

You could base your subform on a Query containing activitydate, starttime and
endtime, and a calculated field:

TotalActivityTime: DateDiff("N', [StartTime], [EndTime])

On the Subform's form Footer put a textbox with a control source of either

=Sum([TotalActivityTime])

to display 75 minutes (the duration as an integer), or, if you prefer,

=Sum([TotalActivityTime]) \ 60 & Format(Sum([TotalActivityTime]) MOD 60,
"\:00")
 
my subform is a contious form that the user will need to inout there time.
so will the user be able to enter there time if its based on a query?

John W. Vinson said:
I have a subform that has activityDate, startTime and EndTime. I would like
to display to the user the total time an activity took per row and then a
grand total on the main form
Activitydate StartTime EndTime TotalActivityTime
01/02/2009 8:00 AM 8:30 AM 30 min
01/03/2009 9:00 AM 9:15 AM 15 Min
01/03/2009 9:25 AM 9:55 AM 30 min

GrandTotalActivityTime 1 hour 15 min

TotalActivityTime and GrandTotalActivityTime are unbound
Click to expand...

You could base your subform on a Query containing activitydate, starttime and
endtime, and a calculated field:

TotalActivityTime: DateDiff("N', [StartTime], [EndTime])

On the Subform's form Footer put a textbox with a control source of either

=Sum([TotalActivityTime])

to display 75 minutes (the duration as an integer), or, if you prefer,

=Sum([TotalActivityTime]) \ 60 & Format(Sum([TotalActivityTime]) MOD 60,
"\:00")
Click to expand...
 
I am getting an #error in the textbox.

This is the code that I have in the totalactivitytime control source
=DateDiff("n",[Starttime],IIf([Starttime]>[Endtime],CDate(1+[endtime]),[endtime]))

This is what I have in the grandtotalactivitytime control source:
=Sum(nz([Forms]![frmmain]![frmsubform]![Form]![totalactivitytime]\60 &
Format([Minutes] Mod 60,"\:00")))

The subform is a contious form
 
my subform is a contious form that the user will need to inout there time.
so will the user be able to enter there time if its based on a query?
Click to expand...

Just what is the user entering? Their start time and end time (which WILL
work, and Access will calculate the difference)? Or are they entering the
start time, the end time, and the TotalActivityTime manually? If so, what's to
stop them from entering 10:00AM, 10:05AM and 16 hours TotalActivityTime?

Any field which can (reliably) be calculated SHOULD be calculated, not stored.
 
They are entering the activitydate,starttime and endtime. The
totalactivitytime is there to show them how long they spent on the activity.
Its therefore a calculated field on the form and is not bound to anything.

This is the code that I have in the totalactivitytime control source in the
subform:
=DateDiff("n",[Starttime],IIf([Starttime]>[Endtime],CDate(1+[endtime]),[endtime]))

This is what I have in the grandtotalactivitytime control source on the main
form:
=Sum(nz([Forms]![frmmain]![frmsubform]![Form]![totalactivitytime]\60 &
Format([Minutes] Mod 60,"\:00")))

The subform is a contious form
 
They are entering the activitydate,starttime and endtime. The
totalactivitytime is there to show them how long they spent on the activity.
Its therefore a calculated field on the form and is not bound to anything.

This is the code that I have in the totalactivitytime control source in the
subform:
=DateDiff("n",[Starttime],IIf([Starttime]>[Endtime],CDate(1+[endtime]),[endtime]))

This is what I have in the grandtotalactivitytime control source on the main
form:
=Sum(nz([Forms]![frmmain]![frmsubform]![Form]![totalactivitytime]\60 &
Format([Minutes] Mod 60,"\:00")))

The subform is a contious form
Click to expand...

You can sum *FIELDS* - you cannot sum *CONTROLS*.

Base the Form on a Query including the starttime, endtime, and the calculated
totalactivity time. Bind the form totalactivitytime control to the
(uneditable, calculated) totalactivitytime field in the Query. You'll then be
able to sum it.
 
Back
Top