DCOUNT using multiple fields and criteria

[RogerC]

Can I use DCOUNT to count the number of occurances of an item in one field but only when another field has a specific value in the same row as the item? Example: I have a column named COLOR and a column named IN_STOCK. I want to count the number of times BLUE is listed under the COLOR field but only when YES is shown for it under the IN_STOCK field. I don't know how to include the second criteria in a DCOUNT (and HELP for DCOUNT doesn't show an example of multiple crieria). Thanks!

 

[bat17]

From Access97 Help

The following example returns the number of orders shipped to the United Kingdom after January 1, 1995. The domain is an Orders table. The criteria argument restricts the resulting set of records to those for which ShipCountry equals UK and ShippedDate is greater than 1-1-95.

intX = DCount("[ShippedDate]", "Orders", _

"[ShipCountry] = 'UK' AND [ShippedDate] > #1-1-95#")

In the next example, the criteria argument includes the current value of a combo box called ShipCountry. The values in the ShipCountry combo box come from the field ShipCountry in an Orders table. Note that the reference to the control isn't included in the quotation marks that denote the strings. This ensures that each time the DCount function is called, Microsoft Access will obtain the current value from the control.

intX = DCount("[ShippedDate]", "Orders", "[ShipCountry] = '" _

& Forms!Orders![ShipCountry] & "'AND [ShippedDate] > #1-1-95#")

HTH

Peter