sum field on condidtion...

[adsfx]

Hi,

I have a textbox field that displays a value on condition-

=IIf [commselect]=1,[commission],([commission])*[totsales])/100

But I want to sum this value depending on condition and have tried-

=IIf[commselect]=1 then sum[commission]

ne ideas?

 

[GoDawgs]

Try something like this:

=iif([commselect]=1,sum([commission]))

That should do the trick...assuming that you are putting this in the footer of a grouping on the report, so it gives you the sum of commission in that grouping. If you need something else (the sum of all commissions, the sum of all commissions where commselect is 1, etc) let us know, I couldn't tell from the way you described it. Hope that helps.

Kevin

 

[adsfx]

cheers kevin,
though now im not sure im going bout this the right way!

I am working on a report which displays a list of clients, how much they sold and how much commission they are charged.

The user can choose wether the commission value is treated as a percentage of the total sold or sterling.
In the table is the commission value(gen number) and a field which is either 1 or 2 (1=sterling 2=percent).
When the report is formatted the commission is shown next to the client:

=IIf [commselect]=1,[commission],([commission])*[totsales])/100

I now need to total the commission. I think I need to total the sterling and the percent separately then add them together.
Can this be done with textboxes or should i be using the reports query?

thanx Mark

 

[dhookom]

Try:
=Sum(IIf [commselect]=1,[commission],([commission])*[totsales])/100)

To get the sum of sterling
=Abs(Sum([CommSelect]=1 * [Commission]))
To get the sum of percent
=Abs(Sum([CommSelect]=2 * [Commission]*[TotSales]/100))

Duane
MS Access MVP

 

[adsfx]

thanx for the help I got the results I wanted just
by using the code at the top in the reports query
and a textbox sum