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.Driv erID, tblDrivers.DNam e
FROM tblDrivers
WHERE (((tblDrivers.D Name) Like "N*"))
ORDER BY tblDrivers.Driv erID;

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**@NorthStat e.net> wrote in message
news:i3******** *************** *********@4ax.c om...

Suppose I have this simple SQL string...

SELECT tblDrivers.Driv erID, tblDrivers.DNam e
FROM tblDrivers
WHERE (((tblDrivers.D Name) Like "N*"))
ORDER BY tblDrivers.Driv erID;

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**@NorthStat e.net> wrote in
news:i3******** *************** *********@4ax.c om:

Suppose I have this simple SQL string...

SELECT tblDrivers.Driv erID, tblDrivers.DNam e
FROM tblDrivers
WHERE (((tblDrivers.D Name) Like "N*"))
ORDER BY tblDrivers.Driv erID;

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.openR ecordset(strMyS QL, 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**@NorthStat e.net> wrote in message
news:rq******** *************** *********@4ax.c om...

<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.Connectio n

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.Connectio n

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**@NorthStat e.net> wrote in message
news:b4******** *************** *********@4ax.c om...

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.Connectio n

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.Creat eRecordset(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******** ******@newssvr1 7.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**@NorthStat e.net> wrote in message
news:b4******** *************** *********@4ax.c om...

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.Connectio n
>
>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 qryVehiclesNown ers5.SerialNum,
qryVehiclesNown ers5.VDescr, qryVehiclesNown ers5.Owner,
qryVehiclesNown ers5.VehicleJob ID , tblVehicleJobs. Reclaimed,
tblVehicleJobs. VSaleID, tblVehicleJobs. ENF262Written FROM
qryVehiclesNown ers5 INNER JOIN tblVehicleJobs ON
qryVehiclesNown ers5.VehicleJob ID = tblVehicleJobs. VehicleJobID WHERE
(tblVehicleJobs .Reclaimed=No AND tblVehicleJobs. VSaleID Is Null AND
tblVehicleJobs. ENF262Written=T rue);"

Dim dbs As Database, rst As Recordset

' Return reference to current database.
10 Set dbs = CurrentDb
20 Set rst = dbs.OpenRecords et(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 frmVehicleChoos erForm. 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.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxx

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.Creat eRecordset(SQL)
If rs.EOF = False
lngX = Nz(rs!Records, 0)
End If

rs.Close
Set rs = Nothing

[Randy Harris]

"MLH" <CR**@NorthStat e.net> wrote in message
news:a4******** *************** *********@4ax.c om...

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 qryVehiclesNown ers5.SerialNum,
qryVehiclesNown ers5.VDescr, qryVehiclesNown ers5.Owner,
qryVehiclesNown ers5.VehicleJob ID , tblVehicleJobs. Reclaimed,
tblVehicleJobs. VSaleID, tblVehicleJobs. ENF262Written FROM
qryVehiclesNown ers5 INNER JOIN tblVehicleJobs ON
qryVehiclesNown ers5.VehicleJob ID = tblVehicleJobs. VehicleJobID WHERE
(tblVehicleJobs .Reclaimed=No AND tblVehicleJobs. VSaleID Is Null AND
tblVehicleJobs. ENF262Written=T rue);"

Dim dbs As Database, rst As Recordset

' Return reference to current database.
10 Set dbs = CurrentDb
20 Set rst = dbs.OpenRecords et(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 frmVehicleChoos erForm. 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.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxx

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?