Count distinct records to display in unbound field on a report

  • Thread starter Thread starter Federico
  • Start date Start date
F

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?
 
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")
 
Thanks Klatuu for clarifying this.
Federico

Klatuu said:
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


Federico said:
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?
Click to expand...
Click to expand...
 
Back
Top