VBA Totals

[J]

I have a report and I need to display totals in the report footer. I
have been using sub reports for the values I need but it asks me for
the master number about 10 times when I only want it to ask for it
once. I was wondering if there was a way to use VBA to get the totals
that I need and still only need to enter the master number once.
Here's what I need:

1.A text box that displays a total of the records that have 100 AND a
true value in a status check box.

2. A text box that displays the total amount of records that have 100
AND a false value in the status check box.

3. A text box that has a number greater than 0 and less than 100

4. A text box with the average of the sum of 3 separate columns.

5. The total dollar value of the records that meet the criteria of the
1st and 2nd things I need above.

I havn't used VBA in a while and I need as much help as I can get with
it, I apologize if it seems like this is a lot to ask for.

 

[John Spencer]

1.A text box that displays a total of the records that have 100 AND a true
value in a status check box.
Sum of the values
=Sum(IIF([TheField] =100 AND Status = true,[TheField],Null))
If you want a COUNT of the 100s then
= Abs(Sum(TheField] =100 AND Status = true))

2. A text box that displays the total amount of records that have 100
AND a false value in the status check box.
=Sum(IIF([TheField] =100 AND Status = False,[TheField],Null))

3. A text box that has a number greater than 0 and less than 100
=Sum(IIF([TheField] > 0 AND [TheField] < 100 ,[TheField],Null))

4. A text box with the average of the sum of 3 separate columns.
= (NZ(Sum(Column1),0) + NZ(Sum(Column2),0) + NZ(Sum(Column3),0))/ 3

5. The total dollar value of the records that meet the criteria of the 1st
and 2nd things I need above.
=Sum(IIF([TheField] =100,[TheField],Null))

 

[J]

How do the first 2 give me a count? I need to have it so that if there
are 3 "100s" in the table then the number I get back will be a 3. I
don't need to add all the 100s together.

 

[J]

Also, I get an error saying that the expression is too complex, any
idea about what I can do about that?

 

[John Spencer]

The first two don't give you a count. You said you wanted a total. But I
was unsure of what you really wanted so I gave you an option of getting the
sum or getting the count.

As for the error, I noticed that I left off one of the [ in the equations.

= Abs(Sum([TheField] =100 AND Status = true))
Break it down

-- ([TheField] =100 AND Status = true) will return True (-1) or False (0)
-- Sum will add up the -1 and 0
-- Abs will strip off the negative sign

1.A text box that displays a total of the records that have 100 AND a true
value in a status check box.
If you want a COUNT of the 100s then
= Abs(Sum([TheField] =100 AND Status = true))

2. A text box that displays the total amount of records that have 100
AND a false value in the status check box.
= Abs(Sum([TheField] =100 AND Status = False))

3. A text box that has a number greater than 0 and less than 100 (the Sum
of the value or the Count of the rows?)
=Sum(IIF([TheField] > 0 AND [TheField] < 100 ,[TheField],Null))
Or the count of rows
= Abs(Sum(([TheField] > 0 AND [TheField] < 100 ))

4. A text box with the average of the sum of 3 separate columns.
= (NZ(Sum(Column1),0) + NZ(Sum(Column2),0) + NZ(Sum(Column3),0))/ 3

5. The total dollar value of the records that meet the criteria of the 1st
and 2nd things I need above.
=Sum(IIF([TheField] =100,[TheField],Null))