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

How to to put the result of a query into a variable?

P: n/a
Set strFoundCt = ("SELECT COUNT(*) FROM (SELECT DISTINCT Entity_ID FROM
qrySearch" & strPxSx)

This doesn't work, but hopefully describes what I'm trying to do.

I have several different queries (e.g. qrySearch29) that are run based on
user selection criteria. User criteria is saved in the strPxSx variable.

The problem is getting a count of DISTINCT records found. Is there any way
to get the result of the above SQL statement into a variable? I'd like to
do this programmatically (so I can avoid creating additional queries).

thx in advance
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Did you try DCount?
strFoundCt = DCount("*","NameOfQuery")
You will need to add a where clause whenever applicable.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.
"deko" <dj****@hotmail.com> wrote in message
news:xI*****************@newssvr29.news.prodigy.co m...
Set strFoundCt = ("SELECT COUNT(*) FROM (SELECT DISTINCT Entity_ID FROM
qrySearch" & strPxSx)

This doesn't work, but hopefully describes what I'm trying to do.

I have several different queries (e.g. qrySearch29) that are run based on
user selection criteria. User criteria is saved in the strPxSx variable.

The problem is getting a count of DISTINCT records found. Is there any way to get the result of the above SQL statement into a variable? I'd like to
do this programmatically (so I can avoid creating additional queries).

thx in advance

Nov 12 '05 #2

P: n/a

"deko" <dj****@hotmail.com> wrote in message
news:xI*****************@newssvr29.news.prodigy.co m...
Set strFoundCt = ("SELECT COUNT(*) FROM (SELECT DISTINCT Entity_ID FROM
qrySearch" & strPxSx)

This doesn't work, but hopefully describes what I'm trying to do.

I have several different queries (e.g. qrySearch29) that are run based on
user selection criteria. User criteria is saved in the strPxSx variable.

The problem is getting a count of DISTINCT records found. Is there any way to get the result of the above SQL statement into a variable? I'd like to
do this programmatically (so I can avoid creating additional queries).

thx in advance


dim myset as recordset
set myset = currentdb.openrecordset("select count(*) as expr1 from...")
strFoundCt = myset("expr1")
myset.close
set myset = nothing

Nov 12 '05 #3

P: n/a
thanks dogwalker

but...

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT COUNT(*) AS EidCt
FROM qrySearch" & strPxSx)
strFoundCt = rst("EidCt")
rst.Close
Set rst = Nothing

returns a count of *all* items in the record set...

I also tried:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT COUNT(*) AS EidCt FROM (SELECT
Entity_ID FROM qrySearch" & strPxSx)
strFoundCt = rst("EidCt")
rst.Close
Set rst = Nothing

....but no luck - nothing is returned...

here's an example of what the rst might look like:

Entity_ID | Name | Company

10 | Bill | Intel
11 | Bob | Nortel
11 | Tim | Sun
12 | Tom | PG&E
13 | Sam | Matel
13 | Ned | Ford

the count I'm interested in is a distinct count of Eid (4), rather than all
items (6)

I've also tried DCount("Eid", "qrySearch29") but as far as I know DCount
does not support dinstinct counts.

thanks again...

"deko" <dj****@hotmail.com> wrote in message
news:xI*****************@newssvr29.news.prodigy.co m...
Set strFoundCt = ("SELECT COUNT(*) FROM (SELECT DISTINCT Entity_ID FROM
qrySearch" & strPxSx)

This doesn't work, but hopefully describes what I'm trying to do.

I have several different queries (e.g. qrySearch29) that are run based on
user selection criteria. User criteria is saved in the strPxSx variable.

The problem is getting a count of DISTINCT records found. Is there any way to get the result of the above SQL statement into a variable? I'd like to
do this programmatically (so I can avoid creating additional queries).

thx in advance

Nov 12 '05 #4

P: n/a
you need the correct sql statement
i would try another post with a subject indicating that
"deko" <dj****@hotmail.com> wrote in message
news:m_*****************@newssvr29.news.prodigy.co m...
thanks dogwalker

but...

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT COUNT(*) AS EidCt
FROM qrySearch" & strPxSx)
strFoundCt = rst("EidCt")
rst.Close
Set rst = Nothing

returns a count of *all* items in the record set...

I also tried:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT COUNT(*) AS EidCt FROM (SELECT Entity_ID FROM qrySearch" & strPxSx)
strFoundCt = rst("EidCt")
rst.Close
Set rst = Nothing

...but no luck - nothing is returned...

here's an example of what the rst might look like:

Entity_ID | Name | Company

10 | Bill | Intel
11 | Bob | Nortel
11 | Tim | Sun
12 | Tom | PG&E
13 | Sam | Matel
13 | Ned | Ford

the count I'm interested in is a distinct count of Eid (4), rather than all items (6)

I've also tried DCount("Eid", "qrySearch29") but as far as I know DCount
does not support dinstinct counts.

thanks again...

"deko" <dj****@hotmail.com> wrote in message
news:xI*****************@newssvr29.news.prodigy.co m...
Set strFoundCt = ("SELECT COUNT(*) FROM (SELECT DISTINCT Entity_ID FROM
qrySearch" & strPxSx)

This doesn't work, but hopefully describes what I'm trying to do.

I have several different queries (e.g. qrySearch29) that are run based on user selection criteria. User criteria is saved in the strPxSx variable.
The problem is getting a count of DISTINCT records found. Is there any

way
to get the result of the above SQL statement into a variable? I'd like to do this programmatically (so I can avoid creating additional queries).

thx in advance


Nov 12 '05 #5

P: n/a
So fix your query so it only returns the records you want, and then if
you gotta, use

lngCount = rst.Recordcount

rst.close
set rst=nothing...
Nov 12 '05 #6

P: n/a
Got it.... thanks for the help!

Dim strFilter As String
Dim lngFoundCt As Long
strFilter = "qrySearch" & DLookup("PxSx", "tblTemp")
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT Entity_ID FROM " &
strFilter)
lngFoundCt = rst.RecordCount
rst.Close
Set rst = Nothing

MsgBox lngFoundCt


"dogwalker" <d@g.com> wrote in message
news:Db*******************@news20.bellglobal.com.. .
you need the correct sql statement
i would try another post with a subject indicating that
"deko" <dj****@hotmail.com> wrote in message
news:m_*****************@newssvr29.news.prodigy.co m...
thanks dogwalker

but...

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT COUNT(*) AS EidCt
FROM qrySearch" & strPxSx)
strFoundCt = rst("EidCt")
rst.Close
Set rst = Nothing

returns a count of *all* items in the record set...

I also tried:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT COUNT(*) AS EidCt FROM (SELECT
Entity_ID FROM qrySearch" & strPxSx)
strFoundCt = rst("EidCt")
rst.Close
Set rst = Nothing

...but no luck - nothing is returned...

here's an example of what the rst might look like:

Entity_ID | Name | Company

10 | Bill | Intel
11 | Bob | Nortel
11 | Tim | Sun
12 | Tom | PG&E
13 | Sam | Matel
13 | Ned | Ford

the count I'm interested in is a distinct count of Eid (4), rather than

all
items (6)

I've also tried DCount("Eid", "qrySearch29") but as far as I know DCount
does not support dinstinct counts.

thanks again...

"deko" <dj****@hotmail.com> wrote in message
news:xI*****************@newssvr29.news.prodigy.co m...
Set strFoundCt = ("SELECT COUNT(*) FROM (SELECT DISTINCT Entity_ID FROM qrySearch" & strPxSx)

This doesn't work, but hopefully describes what I'm trying to do.

I have several different queries (e.g. qrySearch29) that are run based on user selection criteria. User criteria is saved in the strPxSx variable.
The problem is getting a count of DISTINCT records found. Is there
any way
to get the result of the above SQL statement into a variable? I'd

like to do this programmatically (so I can avoid creating additional queries).

thx in advance



Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.