Count distinct records to display in unbound field on a report

[Federico]

I have a report that I use to show grand totals from different queries.

In the PCR table, I have a field "OrigVialBC" that can have duplicated
entries. I wanted to obtain the total of entries for each OrigVialBC and did
so by creating the query:

SELECT count(Tmp.PCR.OrigVialBC) AS TotTested FROM [SELECT DISTINCT
OrigVialBC FROM PCR; ] AS Tmp;

....which runs without problems. However, when I paste that code into the
Controlsource of a Textbox or a RowSource of a lstbox in the detail section
of a report, Access returns the error message (?Name) or ("The record source
'SELECT ... ' specified in this report does not exist").

Any idea why this occurs?

 

[Klatuu]

You can't do it directly that way. What you can do is save your query as a
stored query and use a DCount Domain Aggregate function in the control source
that references the query:
=DCount("*","QueryName")

 

[Federico]

Thanks Klatuu for clarifying this.
Federico

You can't do it directly that way. What you can do is save your query as a
stored query and use a DCount Domain Aggregate function in the control source
that references the query:
=DCount("*","QueryName")

--
Dave Hargis, Microsoft Access MVP

I have a report that I use to show grand totals from different queries.

In the PCR table, I have a field "OrigVialBC" that can have duplicated
entries. I wanted to obtain the total of entries for each OrigVialBC and did
so by creating the query:

SELECT count(Tmp.PCR.OrigVialBC) AS TotTested FROM [SELECT DISTINCT
OrigVialBC FROM PCR; ] AS Tmp;

...which runs without problems. However, when I paste that code into the
Controlsource of a Textbox or a RowSource of a lstbox in the detail section
of a report, Access returns the error message (?Name) or ("The record source
'SELECT ... ' specified in this report does not exist").

Any idea why this occurs?