Count Distinct displayed on subform

[gmazza via AccessMonster.com]

Hi there,
I have read many many articles on this website and I still can't get this
right.
I want a count of distinct RefNum displayed on my subform.
My subform displays 3 records, 2 with the same RefNum, so I would like the
text field on my subform to say 2, because there are 2 different Refnum's out
of the 3 records.
I have a query, qryCount that has this code:
SELECT DISTINCT dbo_DDXLMO.Refnum FROM dbo_DDXLMO;

I then have a text box on my subform that has the following in the Control
Source:
=DCount("*","[qryCount]")

It keeps coming back with 3.
What am I doing wrong here?

Thanks in advance.

 

[John Spencer]

If you run qryCount how many records does it return? If it returns two then
the DCount should return two. If it returns 3 then the Dcount will return 3.

I know that is basic, but that is the first thing I would check.

Next I would check and make sure that qryCount was the query name. I have
made that type of error often enough - one query named qryCount and another
one named qry_Count.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County