473,239 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

Count unique records in report with dynamic recordsource

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
5 8251
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: Neil | last post by:
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms with ODBC linked tables. In one form, the user needs to be able to check a box to select one or more records. This is...
4
by: sherkozmo | last post by:
SQL2000 - AccessXP I built an adp file with a stored procedure from SQL as follows: SELECT * FROM Z_mis_sjk_job_code_access WHERE job_code=@JobCode UNION ALL SELECT * FROM...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
1
by: Simon Matthews | last post by:
Hope someone can help an Access beginner! I've just started keeping my surgical logbook on access and it's a simple flat-file affair. I have created several queries that will list cases...
9
by: mooseshoes | last post by:
All: I'm using Access 2000 on a Windows XP platform. My goal is to use a form to gather user criteria which I will then parse into a useable SQL string. At this point I would like to open one...
2
by: SJM | last post by:
I have a report that displays records of real estate properties. It is possible for each property to appear a number of times for various reasons. Each record however is unique. What I would like...
2
by: Stephen | last post by:
Hello all Run into a problem with a count query in access. It seems to count either all the results or none depending on criteria even though I have a query that selects the difference in...
5
by: Soccer5 | last post by:
Trying to Count records on a report that meet a certain criteria. Have a text box in the Report Footer that has the following in the Control Source: =Count(="S") This does not work. It...
7
by: AccessHunter | last post by:
Hi, Please help with this problem. I have a report that displays a list of Case/Judge Records. using the following fields, Case Nbr, Case Seq Nbr, Judge Name, Attorney name 723187, 1...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.