If I save the query as qryTest and then set the control source to
=Dlookup("[CountOfPID]","QryTest") then that works.
If I try
=Dlookup("[CountOfPID]","SELECT DISTINCT Count(qryReport1.PID) AS CountOfPID
FROM qryReport1 WHERE (((qryReport1.SID) 374)) OR (((qryReport1.SID)=376))
") that doesn't work.
To use Dlookup does the SELECT statement need to be saved as a Query? If
this is true then this solution won't work for me because the query is
dynamically generated based on multiple selections in a multi-select combo.
I guess I could dynamically save the query but maybe there's a better
solution.
Thanks
"PC Datasheet" <sp**@nospam.spam> wrote in message
news:az*******************@newsread3.news.atl.eart hlink.net...
Put the following in the control source of a textbox:
=Dlookup("[CountOfPID]","NameOfYourQuery")
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Terri" <Te***@spamaway.com> wrote in message
news:c1**********@reader2.nmix.net... I have a form with a multi-select combo. I dynamically build a SELECT
statement, open a report, and set the recordsource to my dynamic SELECT
statement. I count the records returned in the report by setting a text
box to =Count(*). This works fine.
Now I want to count the unique records in my report. I can dynamically
create a SELECT statement that counts unique records. My statement looks
like this:
SELECT DISTINCT Count(qryReport1.PID) AS CountOfPID
FROM qryReport1
WHERE (((qryReport1.SID)=374)) OR (((qryReport1.SID)=376))
What can I do to set a textbox on the report to return the figure
returned by the above query. I can't set this textbox directly. I am not to
familiar with functions or ADO but will go there if necessary.
Thanks