Sum a limited number of items in a Continuous Form

[SeaRox]

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]

Is the form built on a query?

 

[SeaRox]

CardGunner,

Yes it is built on a query.

 

[SeaRox]

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,

 

[SeaRox]

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?

 

[CEH]

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])

 

[SeaRox]

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.

 

[WayneRyan]

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

 

[SeaRox]

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?