Calculated controls based on Weekday() in form

[DJDocEBrown]

Here is the basic info:

Table has Checkbox column
BusinessDate column (mm/dd/yyyy format)
OrderTotal column (in dollars)
There are others but these are the relevant ones

In the header of the form I have a calculated control box with the following control:

=Sum(Abs(nz([Checkbox],0))*nz([OrderTotal],0)),0)

Which works just fine. The user is presented with a list of all the orders from this table in the form. They check the checkboxes and the control in the header shows the total of the records checked.

Here is my task:

I need to make 7 of these calculated controls, one for each day of the week. Is this possible? This way the user can see the order totals for each day of the week they have selected.

I tried:
=IIf(Weekday([BusinessDate])=2,Sum(Abs(nz([Pull],0))*nz([OrderTotal],0)),0)

but it still sums up the whole week as the expression holds true.

I do not know VB, and I'm sure there is a really neat and easy way to do it in there. I just don't know how.

As a side note, I also can not get the me.dirty trick to work so the update happens as the boxes are checked.

thanks in advance!

 

[MarkK]

I don;t understand this requirement . . .

see the order totals for each day of the week they have selected.

Do you want to sum the orders for all the Tuesdays?

 

[pbaldy]

I think you want to reverse the formulas:

=Sum(IIf))

not

=IIf(Sum())

 

[DJDocEBrown]

Yes I want to sum the checked amounts for each day of the week.

So there will be 7 calculated boxes in the header (one for each day of the week) and then an additional box that sums all the checked records regardless of the day of the week (This formula I already have working just fine).

I will try switching the IIF(sum()) to sum(iif()) and see what happens.

 

[MarkK]

True = -1 and False = 0, so to only select data for a Monday, let's say, you can multiply in the boolean expression Abs(Weekday(BusinessDate) = 2), which will equal 1 when BuinessDate is a Monday, and zero for all other days of the week. In this way you can select data for a single day.

=Sum(Abs(Weekday(BusinessDate)=2) * Abs(Nz(Pull,0)) * Nz(OrderTotal,0) )