Sumif Statement in Access Report

[tigers]

Hello,
We are trying to write a sumif statement in Access at the end of a report but it is not working. What we want to do is say the following:

In our lingo: For all matters (records) where the Contingency field equals "Y", sum the dollar amount.

I tried to write it in more general lingo:
IF FieldA = Y THEN total the amount field for that record OTHERWISE don't total the amounts

We've tried sumiif and DSUMs but it's not working. Any ideas on what we might be doing wrong? Thanks!

[PamelaDV]

My first thought might not be the easiest or quickest - but you may need to just loop through the records to get the total you want. Reports in Access don't work the same way a report in Excel would. They aren't quite as dynamic.

My first thought is it would go something like this:

Report_OnOpen

Dim ReportSum as long
Dim db as database
Dim rstReport as recordset

db = currentdb()

rstReport = db.openrecordset("qryName", OpenDynaset) 'Where qryName is the name of the query or table feeding your report

ReportSum = 0
rstReport.movefirst
Dountil rstreport.eof
if rstreport!contingency = "Y" Then
ReportSum = ReportSum + rstReport!Total
End If
rstreport.movenext
Loop

me!TotalSum = reportSum


This code is totally untested - just a suggestion - I'm sure there may be better and quicker ways of doing this. There's no error trapping or anything in this code either. Just my initial thoughts of how I would begin to get the total on the report that you are looking for.

Anyone feel free to correct me if there is an easier way to do this.

PamelaDV
"Try Not. Do or Do Not. For There is No Try."

[GolferGuy]

IF FieldA = Y THEN total the amount field for that record OTHERWISE don't total the amounts

If I understand this correctly, you only want to include the amount for this record in the total if FieldA = "Y".
But, are you still printing this amount even if FieldA <> "Y" (FieldA not equal to "Y")? I'm going to assume you do NOT want to print this figure if you are not going to include it in the total. Then, the control source for this text box will be: =IIf([FieldA]="Y", fldAmount, 0) this assumes fldAmount as the name of the field to sum. Be sure to change this to the actual field you want to sum.

You must make sure that the text box this formula is in is NOT named fldAmount. (This is VERY important to rename this text box. It CAN NOT have the same name as fldAmount, what ever the real name is! And no other controls in this report can have the same name. I personally would name the text box "txtfldAmount" for whatever that is worth.)

Then where you want to print the total, you will have just about the same formula. Look carefully, there is a difference. (the "Sum( )" is the difference.)
=Sum(IIf([FieldA="Y", fldAmount, 0))
You will also need to change the "Running Sum" property (Data tab) as Over Group or Over All. Play with that to see which is right for your circumstance.

Hope this helps,
Vic

[tigers]

Thank you to everyone that responded. It turned out that we were just ommitting the single quotes... Here is what we ended up with...

=DSUM("[AR_91to_120]","[amountover90days]","[contingency]='y'")

Thank you again!!!

[GolferGuy]

May I suggest that the next time you post a question you include the code you have tried. My answer would have been a whole lot easier. Using the term "sumif" (Which Access does not have and I think Excel does) lead me to believe you were so new to Access you did not know about DSum, how to use =Sum() within a Report, or how reports handle summing as they print.
By giving a little more information in your question, you give great assistance to the people you are asking to help you. It is a great trade-off.
Thanks,
Vic