dcount() distinct values

[Guest]

Is there are way to count distinct values?

For instance,
If DCount("Distinct [Current Auction #]", "tbl_Ordering_File_Edit") > 0 Then


Thanks

 

[Guest]

Not like that. The DCount will return a value based on a criteria. If you
want to get more than one value, you will need to use a DCount for each set
of criteria. If you have several you need to use, you could use a Totals
query to return all you need and open it as a recordset and retrieve the
values from the individual fields. Here is an example that returns the Name
of each ITM and the number of records each has in the table:

SELECT Actual_res_export.ITM, Count(Actual_res_export.ITM) AS CountOfITM
FROM Actual_res_export
GROUP BY Actual_res_export.ITM;

Here is a variation that limits the count based on the value in another field:

SELECT Actual_res_export.ITM, Count(Actual_res_export.ITM) AS CountOfITM,
Actual_res_export.BillCat
FROM Actual_res_export
GROUP BY Actual_res_export.ITM, Actual_res_export.BillCat
HAVING (((Actual_res_export.BillCat)="ODCB"));

To use this:
Set rst = CurrentDb.OpenRecordset("MyQueryName")
Debug.Print "ITM " & rt!ITM & " Has " & rst!CountOfITM & " " & rst!BillCat &
" Records:

 

[Allen Browne]

Okay, this gets asked reasonably often, so here is a new article:
ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.