Using SUM Function On Access Reports

[bigmerf]

I have created a report using Access 2003 that has 4 grouping levels.

1st grouping = by Sales Rep
2nd grouping = by Month
3rd grouping = by Sales Code
4th grouping = by Location

I have a field called "SalesDollars" that I need to SUM for each rep, for each month, for each sales code, for each location.

Is there any way possible that I can create a SUM function where I can say:

SUM([SalesDollars} WHERE [SalesCode] = "A" AND [Location] = "B"

I tried creating a SQL query similar to this, but when applying it to the field on the report, I keep getting a syntax error.

Is there an easier way to do this, such as the control source builder perhaps?

Please help!

 

[snyperx3]

=Sum(IIf([SalesCode] = "A" And [Location] = "B",[SalesDollars],0))

Should do the trick for ya.

-Pete

 

[dhookom]

That syntax will work for the stated specification but I think that is only the tip of the ice berg. I could be wrong...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bigmerf]

Thanks snyperx3! As you were typing in your response I started to play with "If" statements myself. I tried something similar to yours:

=IIf([SalesCode] = "A",IIF([Location] = "B", SUM([SalesDollars]), 0))

It seems to work pretty good.

Thanks for your help!!

 

[snyperx3]

no problem =]

-Pete