Summing up total time in a contious form

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
 
T

Tom van Stiphout

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
 
J

John W. Vinson

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

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")
 
J

JOM

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

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")
 
J

JOM

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
 
J

John W. Vinson

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?

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.
 
J

JOM

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
 
J

John W. Vinson

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top