|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 22nd, 2009, 05:45 AM
posted to microsoft.public.access.forms
|
|||
|
|||
Summing up total time in a contious form
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 
|
|
#2
October 22nd, 2009, 06:12 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Summing up total time in a contious form
On Wed, 21 Oct 2009 21:45:01 -0700, JOM
wrote: On the main form you could have a textbox with a ControlSource of: =Sum(Forms!MyForm!MySubformControl.Form!TotalActiv ityTime (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
|
|
#3
October 22nd, 2009, 06:48 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Summing up total time in a contious form
On Wed, 21 Oct 2009 21:45:01 -0700, JOM wrote:
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") -- John W. Vinson [MVP]
|
|
#4
October 22nd, 2009, 05:20 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Summing up total time in 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? "John W. Vinson" wrote: On Wed, 21 Oct 2009 21:45:01 -0700, JOM wrote: 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") -- John W. Vinson [MVP] .
|
|
#5
October 22nd, 2009, 05:39 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Summing up total time in a contious form
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 "Tom van Stiphout" wrote: On Wed, 21 Oct 2009 21:45:01 -0700, JOM wrote: On the main form you could have a textbox with a ControlSource of: =Sum(Forms!MyForm!MySubformControl.Form!TotalActiv ityTime (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 .
|
|
#6
October 22nd, 2009, 05:46 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Summing up total time in a contious form
On Thu, 22 Oct 2009 09:20:01 -0700, JOM wrote:
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" wrote: On Wed, 21 Oct 2009 21:45:01 -0700, JOM wrote: 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 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. -- John W. Vinson [MVP]
|
|
#7
October 22nd, 2009, 06:44 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Summing up total time in 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 "John W. Vinson" wrote: On Thu, 22 Oct 2009 09:20:01 -0700, JOM wrote: 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" wrote: On Wed, 21 Oct 2009 21:45:01 -0700, JOM wrote: 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 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. -- John W. Vinson [MVP] .
|
|
#8
October 22nd, 2009, 07:45 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Summing up total time in a contious form
On Thu, 22 Oct 2009 10:44:11 -0700, JOM wrote:
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. -- John W. Vinson [MVP]
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
