counting distinct from a query

[campbell_canuck]

Hi everyone,

I understand how to get a count(distinct *) using multiple queries.
The problem I have is that I have been given a rather complicated query
to use as the record source of a form. I also need to display the
distinct values from one of the fields in the query.

I have Original_Query as the recordsource of Form1

I also have Distinct_Query which is:

select distinct (NumberField) number_count
from Orig_Query

When I try to add a text field with a Control Source set to be
DCOUNT(number_count, [Distinct_Query]), the value displayed is #Name?

When I set the Control Source to be
Count([Distinct_Query]![number_count]) I get Error! as the value.

When I add the sub-query "(select count(number_count) from
Distinct_Query) as distinct_count" to Form1.RecordSource I get a
circular reference error.

Without re-writing the original query or going back to the original
tables and recreating the FROM and WHERE clauses in the original query,
is there any way of displaying the distinct number of values for a
field from that query on the same form. Writing code behind the scenes
is not a preferred option either.

TIA

 

[John Vinson]

When I try to add a text field with a Control Source set to be
DCOUNT(number_count, [Distinct_Query]), the value displayed is #Name?

The syntax is wrong: the arguments to DCount must be text strings. Try

=DCount("*", "[Distinct_Query]")

John W. Vinson[MVP]

 

[campbell_canuck]

When I try to add a text field with a Control Source set to be
DCOUNT(number_count, [Distinct_Query]), the value displayed is #Name?

The syntax is wrong: the arguments to DCount must be text strings. Try

=DCount("*", "[Distinct_Query]")

John W. Vinson[MVP

That always catches me... TYVM