By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

Count unique records in report with dynamic recordsource

P: n/a
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


Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
DLookup only works with a table or query. So you need to change where you create
the dynamic select statement to creating a querydef. You'll actually gain in
performance doing do this too.

Steve
PC Datasheet
"Terri" <Te***@spamaway.com> wrote in message
news:c1**********@reader2.nmix.net...
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



Nov 12 '05 #4

P: n/a
CJ
Thanks Steve. I can make this work using querydef. I'm concerned about this
approach because I then need to delete the query when the report closes.
What if someone opens the report and then Access crashes. There will be an
error message the next time the report is opened because the query exists.
I'd like to hear about others approaches to solving this issue.

Thanks again....
"PC Datasheet" <sp**@nospam.spam> wrote in message
news:nm******************@newsread1.news.atl.earth link.net...
DLookup only works with a table or query. So you need to change where you create the dynamic select statement to creating a querydef. You'll actually gain in performance doing do this too.

Steve
PC Datasheet
"Terri" <Te***@spamaway.com> wrote in message
news:c1**********@reader2.nmix.net...
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
>
>
>
>



Nov 12 '05 #5

P: n/a
You don't need to delete the query when the report closes! If you run the
querydef routine each time the report opens, you will always have a query for
the report and the query will have the fields and criteria you selected for that
session.

Steve
PC Datasheet
"CJ" <ch***@hrn.org> wrote in message news:c1**********@reader2.nmix.net...
Thanks Steve. I can make this work using querydef. I'm concerned about this
approach because I then need to delete the query when the report closes.
What if someone opens the report and then Access crashes. There will be an
error message the next time the report is opened because the query exists.
I'd like to hear about others approaches to solving this issue.

Thanks again....
"PC Datasheet" <sp**@nospam.spam> wrote in message
news:nm******************@newsread1.news.atl.earth link.net...
DLookup only works with a table or query. So you need to change where you

create
the dynamic select statement to creating a querydef. You'll actually gain

in
performance doing do this too.

Steve
PC Datasheet
"Terri" <Te***@spamaway.com> wrote in message
news:c1**********@reader2.nmix.net...
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
> >
> >
> >
> >
>
>



Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.