grouping items in a report

[jim Normile]

1. I am designing a report which groups transactions
for clients by Month.......so I have set up the query and
in the report I have put in a grouping level by month...so
far so good.....
2. then within the month level i have put in a
sublevel for the individual clients and it collects the
transactions for the clients and groups them by
client....so far so good
3. an individual client may have one, two or ten
transactions per month and they are listed accordingly
4. in the client level footer i have designed a
calculated field "=Count(*)" to count the number of
transactions per client in the month......so far so good
5. this Counter in the footer is identified
as "Text35" or whatever number is appropriate
6. In the Month Footer i want to put in a counter which
will give me the frequency of numbers generated in the
Client level footer.....so I am designing a count(iif)
calculated field which looks at "text35" in the client
level footer to decide frequency.
7. I want to get four levels of frequency,ie 1 per
month; 2 per month;3 per month;4 or more per month
8. So i am designing four calculated fields as follows
in the month footer: First "=Count(iif([Text35]=1,0))" to
count the returns of once per month; Second "=Count(iif
([Text35]=2,0))" to get the 2 value;Third "=Count(iif
([Text35]=3,0))"to get the 3 value; Fourth "=Count(iif
([text35]>=4,0))"to get the four or more value
9. When I try to run it I'm getting what looks like a
parameter prompt for "Text35"
10 Anybody tell me what I'm doing wrong ??? I cant
figure it

Any advice gratefully received.....Payment will be a pint
of Guinness next time you are in Dublin

Thanks

Jim

 

[Mary]

Jim,

I've used the IIF function as follows:

IIF([Text35]>=1, Sum(Text35]), 0)

If this would work for you, you would only need one
calculated field. It would show 0 if Text35 is less than
1, or if Text35 is 1 or more, it would show the total of
Text35.

Sorry if this isn't what you're after. I'm kind of new
at using the IIF function myself, but I've used it in
this way with success.

Mary