Sum a limited number of items in a Continuous Form

SeaRox

Registered User.
Local time
Yesterday, 19:16
Joined
Apr 1, 2008
Messages
29
I am using a continous form for time keeping purposes. It looks somewhat like this:

Project | Week | Mon | Tues | Wed | Thur | Fri | Comments|
ABC |03/31/08| 1 | 0 | 3 | 2 | 0 | Design Work |
XYZ
Etc

The form displays the last month's worth of work. I would like to display the number of hours entered for the current week in the footer. The page already limits the entries to a specific employee. I can't seem to figure out how to sum across colums and entries or how to limit the sum to the current week.

I tried creating a seperate query, which worked but I couldn't figure out how to tie it into the footer.
I tried creating a sub form to display the information but couldn't do that since I am using a continuous form. (If I use a single form I loose key functionality I get from the continuous form.)

I tried using some code in a text box: (txtWeek is a textbox with some code that determines the date for the Monday of the current week.)
=Sum(IIf([Weekof]=[txtWeek],[Monday]+[Tuesday]+[Wednesday]+[Thursday]+[Friday],0))

I also tried:
=IIf([Weekof]=[txtWeek],Sum([Monday]+[Tuesday]+[Wednesday]+[Thursday]+[Friday]),0)

Ideally it would also automatically update itself when they enter new hours.

Any ideas?
 
CardGunner,

Yes it is built on a query.
 
In that query I created a new column that sums the week for eath project called HoursSum but I can't figure out how to sum that.

I created a text box and put this text in the default value field:

=Sum([HoursSum])

but it doesn't seem to do anything,
 
I moved the previous code up to the Control Source and now the sum works.

Can any one help with limiting what is sums to a specific date range?
 
Have you tried dropping the IIF statement? It seems pointless, and just using...
=nz([Monday]+[Tuesday]+[Wednesday]+[Thursday]+[Friday])
Or in a query... something like ...
Total:nz([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])
 
CEH,

I have the sum across the week as you suggest in the query. That sum is for hours worked on a specific project. My employees work on several different projects each week and so I would like to be able to sum all their hours for the week. In your example: sum([Total]).

The form displayes all the hours they have entered for the past month but I want the sum to be limited to the current week. Thus the IIF but now that I have been able to get it to "work" the sum goes if the IIF is true but it still sums all the records.
 
SeaRox,

Curtis has it right, if your textbox displaying the total is txtTotal, use:

Me.txtTotal = nz([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])

and put that code in the OnCurrent & AfterUpdate events of the form. Display when they first get to the record and if they ever change
the data.

Wayne
 
I've played with the suggested code:
Me.txtTotal = nz([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])

If I put it in the form OnCurrent and AfterUpdate events it gives me the total for the record that is selected. I assume because I am using a continuous form and the selected record is also the "current" form.

I tried putting the function in the OnCurrent and AfterUpdate events of the text box in the footer which gives me the total for all the records.

Is there a way to limit what this function sums? Am I using it wrong?
 

Users who are viewing this thread

Back
Top Bottom