Sum based on criteria in multiple fields

[Pat Z.]

Field names:
CreatedBy, CommRep,PaymentDate,PaymentAmount

My recordset should sum the PaymentAmount field if CreatedBy OR CommRep =
CPRYBAL (sales rep name). This works fine when I do this for one Sales Rep
but I have 4 different Sales Reps I need to calculate.

Field: CPrybalTTL: ([Payment Amount])
Total: Sum
Criteria: [CreatedBy]="CPrybal" or [CommRep]="CPRYBAL"

Field: KFetteTTL: ([Payment Amount])
Total: Sum
Criteria: [CreatedBy]="KFette" or [CommRep]="KFette"

As soon as I add the second the rep, the sum for both appear in the
recordset,i.e.
CPrybalttl = 18,309 KFettettl = 18,309
I'm stuck...
Thanks in advance!

 

[TPratt]

You shouldn't put in the criteria section.

It should look more like this:

Field: CPrybalTTL: IIF([CreatedBy]="CPrybal" OR [CommRep]
="CPrybal",[Payment Amount],0)
Total: Sum
Criteria:

Field: KFetteTTL: IIF([CreatedBy]="KFette" OR [CommRep] ="KFette",[Payment
Amount],0)
Total: Sum
Criteria:

and so on...

 

[Pat Z.]

Perfect!! Thanks!!

You shouldn't put in the criteria section.

It should look more like this:

Field: CPrybalTTL: IIF([CreatedBy]="CPrybal" OR [CommRep]
="CPrybal",[Payment Amount],0)
Total: Sum
Criteria:

Field: KFetteTTL: IIF([CreatedBy]="KFette" OR [CommRep] ="KFette",[Payment
Amount],0)
Total: Sum
Criteria:

and so on...

Field names:
CreatedBy, CommRep,PaymentDate,PaymentAmount

My recordset should sum the PaymentAmount field if CreatedBy OR CommRep =
CPRYBAL (sales rep name). This works fine when I do this for one Sales Rep
but I have 4 different Sales Reps I need to calculate.

Field: CPrybalTTL: ([Payment Amount])
Total: Sum
Criteria: [CreatedBy]="CPrybal" or [CommRep]="CPRYBAL"

Field: KFetteTTL: ([Payment Amount])
Total: Sum
Criteria: [CreatedBy]="KFette" or [CommRep]="KFette"

As soon as I add the second the rep, the sum for both appear in the
recordset,i.e.
CPrybalttl = 18,309 KFettettl = 18,309
I'm stuck...
Thanks in advance!