Count Based on Criteria in Multiple Fields

P

Pat Z.

Field names:
CreatedBy, CommRep,PaymentDate,PaymentAmount,Cust_Nbr

My recordset should count the Cust_Nbr field if CreatedBy OR CommRep =
CPRYBAL (sales rep name).

Here is what is in my field row in my query:

Sum(Abs(IIf([CreatedBy]="CPrybal" Or
[Commission_Rep]="CPrybal",[Cust_Nbr],0)))

I'm getting a datatype mismatch when I try to run the query.

I'm stuck...
Thanks in advance!
 
C

Chris

Try this expression:

Sum(IIf([CreatedBy]="CPrybal" Or
[Commission_Rep]="CPrybal",1,0))
 
P

Pat Z.

That works! Thanks!!!

Chris said:
Try this expression:

Sum(IIf([CreatedBy]="CPrybal" Or
[Commission_Rep]="CPrybal",1,0))

Pat Z. said:
Field names:
CreatedBy, CommRep,PaymentDate,PaymentAmount,Cust_Nbr

My recordset should count the Cust_Nbr field if CreatedBy OR CommRep =
CPRYBAL (sales rep name).

Here is what is in my field row in my query:

Sum(Abs(IIf([CreatedBy]="CPrybal" Or
[Commission_Rep]="CPrybal",[Cust_Nbr],0)))

I'm getting a datatype mismatch when I try to run the query.

I'm stuck...
Thanks in advance!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top