Count Based on Criteria in Multiple Fields

G

Guest

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!
 
G

Guest

Try this expression:

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

Guest

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!
Click to expand...
Click to expand...
 

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

Similar Threads

Sum based on criteria in multiple fields 2
Access Dcount (multiple criteria) 3
Ranking based on Multiple Data Fields 10
'Data type mismatch in criteria expression' error in Count query 2
'Data type mismatch in criteria expression' error in Count query 1
Optional record selection based on form's checkbox 1
Problems with IIf() 7
Count Query Over Multiple Fields 1

Top