How do I calculate this? IIF dilema

[seannypoos]

Hi hope you can help -
I have a lesson booking database and am using a report to create an invoice.
The report backs onto a query where you can specify a range of dates (parameter queries) which lists matching lessons in that date range.

Each lesson booking has a lessontype (there are several) with different rates.

In the report footer I have a text box which simply adds the rate field for each matching record ie =Sum([rate]). I can get away with the report footer calc as the query chooses a single customer and there are never that many lessons.

Easy enough BUT there's a snag ....

One lesson type (no.4) is associated with a "block booking" this is worth £140 for an advance booking of 10 lessons. The booking form therefore will appear 10 times alongside other more normal lessons. The problem therefore the invoice gets inflated up to £1400 + (10 x £140) which is wrong,

I've tried creating two text box fields, one which says IIF(lessontype = 4,140,0) and then a similar one which is the inverse IIF(lessontype <> 4, rate, 0) and then adding these two together. The first subtotal works - it only counts a block booking lesson once but the other one always evaluates to zero so unless the query results are blockbooking only the total is obviously incorrect all the time. Any ideas?

Many thanks in advance for any help!

 

[raskew]

Hi -

Why not just one Iif() statement, e.g.

IIF(lessontype = 4,140,[rate])

Bob

 

[seannypoos]

Thanks for the idea John but this is what I tried originally - it doesn't work as all that seems to happen is if the first record is a type 4 it evaluates that one but not any of the other ones ie the total always becomes 140

 

[Kafrin]

You need to do the sum in the same step for it not to just look at the first record, so in your report footer try:

=Sum(Iif(LessonType=4,14,[Rate]))

I've used 14 instead of 140 because presumably to add up correctly you need to split the 140 total over the 10 lessons (which each have LessonType=4).

If you want to break it down further, take the Iif clause form above and put it in a box in your report's detail section. You'll probably still need the full clause above in the footer though, cos reports won't always recognise calculated controls in in other controls.

Alternatively, put the Iif clause as a field in the query behind the report (giving it a sensible name) and then just sum this field in your footer.