Count of Unique Values

Question

I have a table with 1355 records.  When I run a query, such as

Select Distinct TableName.FieldNameX

From TableName

I get 1177 records.  This will of course change as records are added or removed through a form.  There are 12 fields total.

I would like to add a control to the form that displays the number of unique values in TableName.FieldNameX.

I have used the distinct query shown above as the source for a control to show the total unique records, but it always reverts to the total records not the number of unique records.

Example, if I have a zip code table for 30 cities, with 350 rows, and a form to add more zip codes, as needed, I want a contol on the form to indicate the number of cities, not the number of zip codes.

Thank you

Hugh

Answer 1

Create a query:

SELECT DISTINCT fieldname FROM tablename;

To return the count use:

DCount("*","queryname")