# Thread: Sum an IIf Textbox on a MS Access Report

1. Registered User
Join Date
May 2003
Location
UK
Posts
233

## Unanswered: Sum an IIf Textbox on a MS Access Report

How do I Sum a textbox that has an IIF Statement in it.

I have txtGroup, txtArea, txtForensic Plus txtValue and txtDisplay

txtDisplay looks at the first three text boxes and if they match a particular criteria it will display value in txtValue. That all works fine. What I then want to do is display the total of txtDisplay in the Report footer. This is where I come unstuck.

I think I am having a bad day as this sounds simple but I just cant get it, can anyone help?

2. Registered User
Join Date
May 2008
Location
Raleigh, NC
Posts
151
Hi,
I'm not sure what is going wrong. Is the txtDisplay appearing as blank? Or is giving the wrong value? Can you be a little more specific?

Thanx, Stu

3. Registered User
Join Date
May 2003
Location
UK
Posts
233
To normally sum a text box in the Footer section of a report I would.

=Sum[txtDisplay] and because that would then give me the sum of the records in that field. Because that field has an IIF statement in it it simply displays the formula as text.

4. Registered User
Join Date
Apr 2004
Location
metro Detroit
Posts
634
What is the formula in txtDisplay?

5. Registered User
Join Date
May 2008
Location
Raleigh, NC
Posts
151
You said that if the criteria is met in txtDisplay it is displayed in txtValue. I assume there is some reason why you don't use =Sum([txtValue])?

Stu

6. Registered User
Join Date
May 2003
Location
UK
Posts
233
The formula in the txtDisplay text box is
=IIf([txtGroup]=4 And [txtArea]=3 And [txtForensic]=1,[txtValue],0)

This displays the value when the criteria is met and a zero when it is not. This is in the details line of the report and pulls data from a query. This works as I want.

I then want to sum the total of those in the txtDisplay text box in the report footer.

7. Registered User
Join Date
May 2008
Location
Raleigh, NC
Posts
151
Try this;
Create another field, [txtDisplayValue] on the detail line and set its value to =[txtDisplay] and the visible property to false. This should put either the [txtValue] value or 0 into it.
Then place =Sum([txtDisplayValue]) on the report footer.

Hope this works for you!

8. Registered User
Join Date
May 2003
Location
UK
Posts
233
Sorry this gives me the same problem.

In the Report Footer it then just shows as "=Sum([txtDisplayValue]) "

9. Registered User
Join Date
May 2008
Location
Raleigh, NC
Posts
151
OK, I think I've got it! MAYBE....
Change the Control Source of [txtDisplayValue] to =Clng([txtDisplay]) and the Running Sum value to "over all". In the report footer place another text box with the Control Source set to =[txtDisplayValue]

Cross your fingers and pray this works!!!

:-)Stu

10. Registered User
Join Date
Nov 2007
Posts
9
Have you tried summing the IIf statement in your report's footer?

=Sum(IIf([txtGroup]=4 And [txtArea]=3 And [txtForensic]=1,[txtValue],0))

I'm assuming here that the source data for the txtGroup, txtArea, and txtForensic are all Numeric. I'd be interested in seeing a screenshot of your report and possibly the SQL code for the source query that the report is using. This would be helpful in providing suggestions to get you the proper results.

11. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
The short answer is YOU CAN'T. You have to do the calculation for txtDisplay in the report's underlying query. Then you can Sum([TheQueryCalculatedField]) in the report as you would any other field.

12. Registered User
Join Date
May 2008
Location
Raleigh, NC
Posts
151

## I hate to disagree, but...

Originally Posted by StarTrekker
The short answer is YOU CAN'T. You have to do the calculation for txtDisplay in the report's underlying query. Then you can Sum([TheQueryCalculatedField]) in the report as you would any other field.
When I added a field based upon the derived value, made sure it was numeric, and gave it a running sum over all, it worked. Granted I did it on a fairly simple report, but it did work for me.

Stu

13. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Ah yes, I forget about the RunningSum property. I still never use that!

My mistake.

I'll shut up now ^^

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•