DCount function counts the unique values in a record set

Have you ever had a long list of records with possible duplicate values and wanted to get a count that only includes a value one time? The DCount function is what you need to count unique values in a specified set of records. The DCount() function is used in macros, query expressions, or in a calculated control. Here's a sample DCount function to get the total number of orders that were placed on a particular date:

DCount( expr, domain [, criteria] )

The above DCount function syntax has a few must have and optional parts:

Argument	Description
expr	Required part that identifies the field to use to count the records. It can be a string expression identifying a field in a table or query, or an expression that does calculations on a field. For example, it can include a table field name, a form control, or a function. The function can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain	Required part that defines the record set, which can be either a table or query name.
criteria	This is optional and is a string expression that restricts the range of data for the DCount function. It is similar to the WHERE clause in an SQL expression. Remember that any fields used as criteria must also be used in the domainpart of the syntax or the DCount function returns a Null.

Note:  Although the expr argument can perform calculations, the results are not available because the DCount function only gives you a count of the number of records.

Another instance where you might want to use of DCount function is, in a calculated control when you include specific criteria to restrict the data range. For example, to display the number of orders to be shipped to California, set the ControlSource property of a text box to the following expression:

=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

To also include records that have Null fields, use an asterisk. To calculate the number of records in an Orders table, you'd use:

intX = DCount("*", "Orders")

For more on creating queries or using expressions, check out the following:

• Count data by using a query

• Creating an expression

• Adding functions to expressions

• Using expressions in query criteria

  Tip:  Also visit MSDN for a list of all available Access functions.