[RESOLVED] Can you use Sumif for a CalculatedControl in an Access Report?

[rack]

I have a report that has groups. Above the groups, in the report header, I want to show a summary of the total Debits, and total Credits, broken down per industry, Internet, Cable etc.

The report currently shows

RCS
--Supervisor
----Employee
------Debit, Credit, Type.

if I put a control at the top of the report, and say =sum(Credit) it will give me a sum of credit for everything, I need it to break down by Type.

Debit -- Credit -- Type
$50 -- $100 -- Internet
$30 -- $50 -- Cable

Will SumIf work on a report? I don't see if in the access help section, but it seems like it would do the trick.

EDIT1:

Just found the Dsum again.
Tried this, but it keeps asking me for a value for the report?

=DSum([Credit],[Report].[rptCS3Detail],"adjReasonCode='DNU'")

rptCS3Detail, is the report, where the textbox is

Edit2:

Tried a few other ways, but get #Error

=DSum("Credit","rptCS3Detail.RecordSource","rptCS3Detail.Filter")
Was hoping, this would use the report data, and the report filter.
Also tried
=DSum("Credit","qryCS3Detail","rptCS3Detail.Filter")


Edit3:

Well, I found "a" way to do it. But it's not very dynamic, and it's a very patchy/bugy kind of way.

I created a textbox on the form that you pick data from.
I save the Where clause that I send to the report, in that textbox.
I also have to type in, each Item in the tblAdjReasonCode
=DSum("Credit","qryCS3Detail",Forms!frmKCreditDebit!txtGStrWhere & " adjReasonCode='Courtesy Credit - Internet'")

So I guess, now my question is, how can I do this better, easier, dynamic, and cookie cutter?

[rack]

Edit3 isn't working.

Going to try more later, if anyone has a suggestion, or a function/control/guess please let me know.

[anhn]

Add each textbox for each type.
For Total [Credit] of "Internet" [Type], enter:
=-Sum([Credit]*([Type]="Internet"))
If ([Type]="Internet") is True it will be converted to -1, so minus sign is required in front of Sum.
If ([Type]="Internet") is False it will be converted to 0, [Credit]*0 = 0, ie. No added into the Total.

[rack]

Thank you again Anhn. I can never explain how much you always help me, and I really do appreciate your input. You seem to be an excel/access wizard =)

I used your code and here is the result incase someone searches they can also see how to use your code, with the count function.

Your code allowed me to use the filtered data on the report, which is great, because I don't filter it in the query, because I am changing the query on the fly. Thank you again =)

=-Sum([Credit]*([adjReasonCode]="Courtesy Credit - Internet"))
=Count(IIf([adjReasonCode]="Courtesy Credit - Internet",1,Null))

EDIT 1:

Unfortuently your the only one who's responded to me recently, so it won't let me rate your post, but if I could, it would be Approve =)

[anhn]

It's Ok! I am glad to help you. In my company with more than 3000 people, I support about a quater of them with Excel reports and Access databases. That is my main job for the last 15 years. Even that, there are still a lot of things I need to learn in this area.

The formula =-Sum([Credit]*([Type]="Internet")) can be written as:
=Sum(IIf([Type]="Internet",[Credit],Null))

You can also use the Sum() function to return Count:
=Sum(IIf([Type]="Internet",1,0))
or simpler:
=-Sum([Type]="Internet")

Ps.: Don't forget to mark your thread as RESOLVED.