Calculation on Report from huge query

[RonnieDobbs]

First off, I am new to this site and I think I am asking the same thing in differnt ways. I hope this is clearer:

I have a query from one table which gathers office names, fee, off limits and ID of certain assignments. From this I have made a report, which looks like the folowingquery looks the same)

ID Office Fee Off limits

12 japan std non-std
13 germany non-std std
.. .... std std
... ..... non std non std

In the header I have four textboxes that perform total calculations. There could be 4 different situations of fees and off limits for each office, and I need to calculate this and place in header to fit on a single page.

here is the code I am using for each:

=Sum(IIf([offlimits]="std" And [fees]="standard" And [office]="london",1))

=Sum(IIf([offlimits]="non std" And [fees]="non-standard" And [office]="london",1,0))

=Sum(IIf([office]="london" And [offlimits]="non std" And [fees]="standard" Or [fees]="none",1,0))

=Sum(IIf([office]="london" And [offlimits]="non std" And [fees]="standard" Or [fees]="none",1,0))

these disply the totals for london office. However, when I create a new query for just london, not all offices the numbers are slightly different. So the report numbers are off to the actual by a few? ANYone know why??? or does anyone have suggestion how to get these totals on a report?

THANKS

 

[dhookom]

If the text boxes are in an [Office] header or footer, then you can remove the [Office]="london" from all expressions. However, I think your issue is due to not using ()s around your two "Or's". Try:

=Sum(IIf([office]="london" And [offlimits]="non std" And ([fees]="standard" Or [fees]="none"),1,0))

Another expression would be:
=Sum(Abs([office]="london" And [offlimits]="non std" And ([fees]="standard" Or [fees]="none")))
or maybe:
=Sum(Abs([offlimits]="non std" And ([fees]="standard" Or [fees]="none")))


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[RonnieDobbs]

ah, makes sense, i will give it a shot.

thanks.