Criteria

[Guest]

I am trying to create a calculated control on a form. I need to restrict the
count using two criterion. I can only get it to work with one criterion - I
have a correct count if I just use the status criterion below. I need to
also include the peer group restriction. Any ideas on how my syntax is
wrong? Thanks!

This code works (only one criterion):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'")

This code won't work (it's what I need to use with 2 criterion):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'" And
"[Peer_Group_Code] = '11'")

 

[Guest]

The And needs to be inside the quotes. Also, the way you have it coded, you
are expecting [Peer_Group_Code] to be a text fields.
=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' And
[Peer_Group_Code] = '11'")

If [Peer_Group_Code] is a numeric field:
=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' And
[Peer_Group_Code] = 11")

 

[Guest]

Peer_Group_Code is a numeric field - I copied the code in that you shared
below where peer group is numeric and got an error - the control displays
#Name? instead of a number. I verified the field names are correct. What
else is wrong with the syntax, are the quotes misplaced?

The And needs to be inside the quotes. Also, the way you have it coded, you
are expecting [Peer_Group_Code] to be a text fields.
=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' And
[Peer_Group_Code] = '11'")

If [Peer_Group_Code] is a numeric field:
=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' And
[Peer_Group_Code] = 11")

I am trying to create a calculated control on a form. I need to restrict the
count using two criterion. I can only get it to work with one criterion - I
have a correct count if I just use the status criterion below. I need to
also include the peer group restriction. Any ideas on how my syntax is
wrong? Thanks!

This code works (only one criterion):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'")

This code won't work (it's what I need to use with 2 criterion):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'" And
"[Peer_Group_Code] = '11'")

 

[Guest]

Please disregard my previous email - I was missing a bracket - thanks for
your help!

The And needs to be inside the quotes. Also, the way you have it coded, you
are expecting [Peer_Group_Code] to be a text fields.
=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' And
[Peer_Group_Code] = '11'")

If [Peer_Group_Code] is a numeric field:
=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' And
[Peer_Group_Code] = 11")

I am trying to create a calculated control on a form. I need to restrict the
count using two criterion. I can only get it to work with one criterion - I
have a correct count if I just use the status criterion below. I need to
also include the peer group restriction. Any ideas on how my syntax is
wrong? Thanks!

This code works (only one criterion):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'")

This code won't work (it's what I need to use with 2 criterion):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'" And
"[Peer_Group_Code] = '11'")

 

[Guest]

Looks correct to me. The error indicates it can't find a field named
Peer_Group_Code. Have you checked your query to be sure it is correct there?

Peer_Group_Code is a numeric field - I copied the code in that you shared
below where peer group is numeric and got an error - the control displays
#Name? instead of a number. I verified the field names are correct. What
else is wrong with the syntax, are the quotes misplaced?

The And needs to be inside the quotes. Also, the way you have it coded, you
are expecting [Peer_Group_Code] to be a text fields.
=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' And
[Peer_Group_Code] = '11'")

If [Peer_Group_Code] is a numeric field:
=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active' And
[Peer_Group_Code] = 11")

I am trying to create a calculated control on a form. I need to restrict the
count using two criterion. I can only get it to work with one criterion - I
have a correct count if I just use the status criterion below. I need to
also include the peer group restriction. Any ideas on how my syntax is
wrong? Thanks!

This code works (only one criterion):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'")

This code won't work (it's what I need to use with 2 criterion):

=DCount("[Vendor_Nbr]","qry_Zip_Code_Vendors","[Status_Code] = 'Active'" And
"[Peer_Group_Code] = '11'")