Can I perform a DCount of records returned by SQL statement (not a saved query)

[MLH]

Suppose I have this simple SQL string...

SELECT tblDrivers.DriverID, tblDrivers.DName
FROM tblDrivers
WHERE (((tblDrivers.DName) Like "N*"))
ORDER BY tblDrivers.DriverID;

And suppose that its not a saved querydef -
just an SQL string that I cooked up in code
and assigned to a global var strMySQL.

How best to count the records returned by
the query? DCount doesn't seem to be the
tool to do it with.

[Randy Harris]

"MLH" <CR**@NorthState.net> wrote in message
news:i3********************************@4ax.com...

Suppose I have this simple SQL string...

SELECT tblDrivers.DriverID, tblDrivers.DName
FROM tblDrivers
WHERE (((tblDrivers.DName) Like "N*"))
ORDER BY tblDrivers.DriverID;

And suppose that its not a saved querydef -
just an SQL string that I cooked up in code
and assigned to a global var strMySQL.

How best to count the records returned by
the query? DCount doesn't seem to be the
tool to do it with.

That query will select all records from tblDrivers where DName starts with
'N'. The practical solution would be simply:

SELECT Count(*)
FROM tblDrivers
WHERE DName Like 'N*'

If for some reason you absolutely must use that original query, you could
use:

"SELECT Count(*) From (" & strMySQL & ")"

HTH,
Randy

[Bob Quintal]

MLH <CR**@NorthState.net> wrote in
news:i3********************************@4ax.com:

Suppose I have this simple SQL string...

SELECT tblDrivers.DriverID, tblDrivers.DName
FROM tblDrivers
WHERE (((tblDrivers.DName) Like "N*"))
ORDER BY tblDrivers.DriverID;

And suppose that its not a saved querydef -
just an SQL string that I cooked up in code
and assigned to a global var strMySQL.

How best to count the records returned by
the query? DCount doesn't seem to be the
tool to do it with.

Since DCount opens a separate instance of the query, it can't be
done if the sql isn't saved. You can create a recordset, that
uses an SQL string as its source, and get the number of records
from that.

set rs = currentdb.openRecordset(strMySQL, dbOpenSnapshot)
rs.movelast
msgbox "the query returns " & rs.recordcount & " Records"
--
Bob Quintal

PA is y I've altered my email address.

[MLH]

<snip>

That query will select all records from tblDrivers where DName starts with
'N'. The practical solution would be simply:

SELECT Count(*)
FROM tblDrivers
WHERE DName Like 'N*' Sounds like a plan. If X is my variable dim'd as Long,
what VBA syntax is used to assign X its value of the
number of records in tblDrivers WHERE DName Like 'N*'?

If for some reason you absolutely must use that original query, you could
use:

Not at all, Randy. I like the above - as long as I can assign the
count to the variable, X. 'preciate it.

[Randy Harris]

"MLH" <CR**@NorthState.net> wrote in message
news:rq********************************@4ax.com...

<snip>

That query will select all records from tblDrivers where DName starts with'N'. The practical solution would be simply:

SELECT Count(*)
FROM tblDrivers
WHERE DName Like 'N*'

Sounds like a plan. If X is my variable dim'd as Long,
what VBA syntax is used to assign X its value of the
number of records in tblDrivers WHERE DName Like 'N*'?

If for some reason you absolutely must use that original query, you could
use:

Not at all, Randy. I like the above - as long as I can assign the
count to the variable, X. 'preciate it.

In ADO, you could use something like this:

Dim varCount
Dim SQL as string
Dim lngX
Dim cn as New ADODB.Connection

Set cn = CurrentProject.Connection

SQL = "Select Count(*) " & _
"From tblDrivers " & _
"Where DName Like 'N*'"

varCount = cn.Execute(SQL).GetString
lngX = Nz(varCount, 0)

Some comments:
I'm in the habit of using a var with .GetString. I do that in case the query
returns a Null value. Anything other than a var will get an error. In this
particular case, it wouldn't be necessary to do so, because a Count always
returns a value, even if it's 0. If you prefer, you could simply use:

lngX = cn.Execute(SQL).GetString

HTH,
Randy

[MLH]

Thank-you, Randy. To my knowledge, this is my first look
at ADO. Mind you, I've done my homework in DAO and this
looks similar. 'preciate the advice. I was beginning to think
that DAO might be the way to go and I'm glad to get an
introduction to DAO via this issue.

In ADO, you could use something like this:

Dim varCount
Dim SQL as string
Dim lngX
Dim cn as New ADODB.Connection

Set cn = CurrentProject.Connection

SQL = "Select Count(*) " & _
"From tblDrivers " & _
"Where DName Like 'N*'"

varCount = cn.Execute(SQL).GetString
lngX = Nz(varCount, 0)

Some comments:
I'm in the habit of using a var with .GetString. I do that in case the query
returns a Null value. Anything other than a var will get an error. In this
particular case, it wouldn't be necessary to do so, because a Count always
returns a value, even if it's 0. If you prefer, you could simply use:

lngX = cn.Execute(SQL).GetString

HTH,
Randy

I like that extra bit of effort. It may seem redundant to some (like
putting a forward or backward slash at the end when you're DIRing
for a directory at the command prompt to specify a directory rather
than a file). I'm always questioning my work and any little thing to
provide reassurance that stuff is working helps me sleep better.

[Randy Harris]

Personally, I find this DAO/ADO dichotomy absolutely maddening. I curse
Microsoft for the stop - start - misdirection of the whole mess. A lot of
the guru types that post here effectively use them together. I find it
confusing to do so. I stick with ADO - not because it's better - but,
because it is what I am comfortable with.

Perhaps someone else would post some DAO-centric code that will accomplish
the same thing?
"MLH" <CR**@NorthState.net> wrote in message
news:b4********************************@4ax.com...

Thank-you, Randy. To my knowledge, this is my first look
at ADO. Mind you, I've done my homework in DAO and this
looks similar. 'preciate the advice. I was beginning to think
that DAO might be the way to go and I'm glad to get an
introduction to DAO via this issue.

In ADO, you could use something like this:

Dim varCount
Dim SQL as string
Dim lngX
Dim cn as New ADODB.Connection

Set cn = CurrentProject.Connection

SQL = "Select Count(*) " & _
"From tblDrivers " & _
"Where DName Like 'N*'"

varCount = cn.Execute(SQL).GetString
lngX = Nz(varCount, 0)

Some comments:
I'm in the habit of using a var with .GetString. I do that in case the queryreturns a Null value. Anything other than a var will get an error. In thisparticular case, it wouldn't be necessary to do so, because a Count alwaysreturns a value, even if it's 0. If you prefer, you could simply use:

lngX = cn.Execute(SQL).GetString

HTH,
Randy

I like that extra bit of effort. It may seem redundant to some (like
putting a forward or backward slash at the end when you're DIRing
for a directory at the command prompt to specify a directory rather
than a file). I'm always questioning my work and any little thing to
provide reassurance that stuff is working helps me sleep better.

[Douglas J. Steele]

Dim varCount
Dim SQL as string
Dim lngX
Dim rs as DAO.Recordset

SQL = "Select Count(*) As Records " & _
"From tblDrivers " & _
"Where DName Like 'N*'"

Set rs = CurrentDb.CreateRecordset(SQL)
If rs.EOF = False
lngX = Nz(rs!Records, 0)
End If

rs.Close
Set rs = Nothing

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Mm**************@newssvr17.news.prodigy.com.. .

Personally, I find this DAO/ADO dichotomy absolutely maddening. I curse
Microsoft for the stop - start - misdirection of the whole mess. A lot of
the guru types that post here effectively use them together. I find it
confusing to do so. I stick with ADO - not because it's better - but,
because it is what I am comfortable with.

Perhaps someone else would post some DAO-centric code that will accomplish
the same thing?
"MLH" <CR**@NorthState.net> wrote in message
news:b4********************************@4ax.com...

Thank-you, Randy. To my knowledge, this is my first look
at ADO. Mind you, I've done my homework in DAO and this
looks similar. 'preciate the advice. I was beginning to think
that DAO might be the way to go and I'm glad to get an
introduction to DAO via this issue.

>
>In ADO, you could use something like this:
>
>Dim varCount
>Dim SQL as string
>Dim lngX
>Dim cn as New ADODB.Connection
>
>Set cn = CurrentProject.Connection
>
>SQL = "Select Count(*) " & _
> "From tblDrivers " & _
> "Where DName Like 'N*'"
>
>varCount = cn.Execute(SQL).GetString
>lngX = Nz(varCount, 0)
>
>Some comments:
>I'm in the habit of using a var with .GetString. I do that in case the query >returns a Null value. Anything other than a var will get an error. In this >particular case, it wouldn't be necessary to do so, because a Count always >returns a value, even if it's 0. If you prefer, you could simply use:
>
>lngX = cn.Execute(SQL).GetString
>
>HTH,
>Randy

I like that extra bit of effort. It may seem redundant to some (like
putting a forward or backward slash at the end when you're DIRing
for a directory at the command prompt to specify a directory rather
than a file). I'm always questioning my work and any little thing to
provide reassurance that stuff is working helps me sleep better.

[MLH]

I have a sneaky suspicion that CreateRecordset isn't
going to work for me in A97. Am I wrong about that?
Or, is my hunch correct?
I'm guessing that OpenRecordset is the method I'll
use in A97. Here's what I tried but 'yikes' it isn't
working...
MySQL = "SELECT DISTINCTROW qryVehiclesNowners5.SerialNum,
qryVehiclesNowners5.VDescr, qryVehiclesNowners5.Owner,
qryVehiclesNowners5.VehicleJobID , tblVehicleJobs.Reclaimed,
tblVehicleJobs.VSaleID, tblVehicleJobs.ENF262Written FROM
qryVehiclesNowners5 INNER JOIN tblVehicleJobs ON
qryVehiclesNowners5.VehicleJobID = tblVehicleJobs.VehicleJobID WHERE
(tblVehicleJobs.Reclaimed=No AND tblVehicleJobs.VSaleID Is Null AND
tblVehicleJobs.ENF262Written=True);"

Dim dbs As Database, rst As Recordset

' Return reference to current database.
10 Set dbs = CurrentDb
20 Set rst = dbs.OpenRecordset(MySQL)
30 rst.MoveLast
40 Me!TheCount = rst.RecordCount
50 rst.Close
60 Set dbs = Nothing

But error handler says line #20 is a problem...
"The following unexpected error occurred in Sub Form_Open, Line #20,
CBF on frmVehicleChooserForm. 3061: "Too few parameters, Expected 5."
Kind-a-blows my mind. A simple piece of SQL works. For instance: If
MySQL = "select VehicleJobID from tblVehicleJobs;" - there's no
error. Any ideas why? The SQL works find pasted into a query and ran.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x

Dim varCount
Dim SQL as string
Dim lngX
Dim rs as DAO.Recordset

SQL = "Select Count(*) As Records " & _
"From tblDrivers " & _
"Where DName Like 'N*'"

Set rs = CurrentDb.CreateRecordset(SQL)
If rs.EOF = False
lngX = Nz(rs!Records, 0)
End If

rs.Close
Set rs = Nothing

[Randy Harris]

"MLH" <CR**@NorthState.net> wrote in message
news:a4********************************@4ax.com...

I have a sneaky suspicion that CreateRecordset isn't
going to work for me in A97. Am I wrong about that?
Or, is my hunch correct?
I'm guessing that OpenRecordset is the method I'll
use in A97. Here's what I tried but 'yikes' it isn't
working...
MySQL = "SELECT DISTINCTROW qryVehiclesNowners5.SerialNum,
qryVehiclesNowners5.VDescr, qryVehiclesNowners5.Owner,
qryVehiclesNowners5.VehicleJobID , tblVehicleJobs.Reclaimed,
tblVehicleJobs.VSaleID, tblVehicleJobs.ENF262Written FROM
qryVehiclesNowners5 INNER JOIN tblVehicleJobs ON
qryVehiclesNowners5.VehicleJobID = tblVehicleJobs.VehicleJobID WHERE
(tblVehicleJobs.Reclaimed=No AND tblVehicleJobs.VSaleID Is Null AND
tblVehicleJobs.ENF262Written=True);"

Dim dbs As Database, rst As Recordset

' Return reference to current database.
10 Set dbs = CurrentDb
20 Set rst = dbs.OpenRecordset(MySQL)
30 rst.MoveLast
40 Me!TheCount = rst.RecordCount
50 rst.Close
60 Set dbs = Nothing

But error handler says line #20 is a problem...
"The following unexpected error occurred in Sub Form_Open, Line #20,
CBF on frmVehicleChooserForm. 3061: "Too few parameters, Expected 5."
Kind-a-blows my mind. A simple piece of SQL works. For instance: If
MySQL = "select VehicleJobID from tblVehicleJobs;" - there's no
error. Any ideas why? The SQL works find pasted into a query and ran.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx x

It's likely that you've got a syntax error in the WHERE clause. Try running
the code after removing the entire WHERE clause.

What datatypes are Reclaimed and ENF262Written?

[Jana]

MLH:

The 'Too few parameters' error usually means that a query referenced in
a recordset has parameters that you aren't feeding to the query. My
guess is that your query qryVehiclesNowners5 has 5 parameters in
it...First, you'll need create a temporary querydef that is based on
your SQL statement. Then you'll need to pass the parameters to the
querydef object. Once that is done, you can then set your recordset to
the results of the querydef. Here's some sample code:

Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim MySQL As String

MySQL = "SELECT DISTINCTROW qryVehiclesNowners5.SerialNum,
qryVehiclesNowners5.VDescr, qryVehiclesNowners5.Owner,
qryVehiclesNowners5.VehicleJobID , tblVehicleJobs.Reclaimed,
tblVehicleJobs.VSaleID, tblVehicleJobs.ENF262Written FROM
qryVehiclesNowners5 INNER JOIN tblVehicleJobs ON
qryVehiclesNowners5.VehicleJobID = tblVehicleJobs.VehicleJobID WHERE
(tblVehicleJobs.Reclaimed=No AND tblVehicleJobs.VSaleID Is Null AND
tblVehicleJobs.ENF262Written=True);"

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("qryTemp", MySQL)

'repeat once for each parameter, with the ENTIRE parameter within
'one set of brackets. If your parameter is grabbed from a form, use
'something like this:
'qdf![Forms!FormName!FormField] = Forms!FormName!FormField
'otherwise, use something like these:

qdf![Parameter1] = "Whatever you want fed to the first parameter"
qdf![Parameter2] = "Whatever you want fed to the second parameter"

Set rst = qdf.OpenRecordset
rst.MoveLast
Me!TheCount = rst.RecordCount

dbs.QueryDefs.Delete qdf.NAME
rst.close
Set dbs = Nothing

The deleting of the querydef is VERY IMPORTANT, or it will error out
the second time you run it and tell you that the querydef already
exists. Remember that you'll need to wrap date parameters with #
signs, strings with single quotes, etc.

HTH,

Jana

[MLH]

They are both boolean yes-no types
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Fri, 14 Oct 2005 13:53:39 GMT, "Randy Harris" <ra***@SpamFree.com>
wrote:

What datatypes are Reclaimed and ENF262Written?

[MLH]

Sounds promising. I'll give it a shot and
give you a report.

[Jana]

MLH:

Did this work for you? Just curious...

Jana