using the results of a SQL SELECT(COUNT) statement in VBA

[Paul James]

I'd like to run a SQL statement which will return a single number, and
assign this number to a variable.

Here's the SQL statement:

"SELECT COUNT(ReceiptID) from tblReceipt WHERE Matched=0"

How do I tell VBA that I want to assign the single-number result of that
SELECT statement to the variable vMatchedCount?

I've tried:

vMatchedCount = "SELECT COUNT(ReceiptID) from tblReceipt WHERE Matched=0"

but that just assigns the entire statement as a text string to the variable.

I tried the same thing without the quotation marks, but that produces the
error "Expected: expression."

There's nothing wrong with the SQL statement itself. I tested it as a query
in the database window and it works fine. I just can't figure out how to
get it to assign the result to a variable in VBA. Could someone please tell
me how do do this?

Thanks in advance,

Paul

 

[Dan Artuso]

Hi,
You would have to open a recordset against the statement and then assign
the value to a variable OR you can use DCount

vMatchedCount = DCount("[ReceiptId]","tblReceipt","Matched = 0"

 

[Paul James]

Thanks, Dan.

I like the idea of using DCount. But could you please also tell me how to
"open a recordset against the statement"? (I'm using DAO objects in my
database).

TIA

 

[Paul Johnson]

The dCount will do the job. (Be sure to use a close parenthesis in the
dCount function.)

If you're curious about opening recordsets, here's a taste:

Dim strSQL as String
Dim db as Database
Dim rst as RecordSet

strSQL = "SELECT COUNT(1) from tblReceipt WHERE Matched=0"
' Use of dummy (1) explained below **
Set db = CurrentDb
Set rst = db.OpenRecordSet(strSQL)

' This recordset will only ever have one record in it, since you are
performing a single aggregate function (count) with no grouping. You won't
need to or be able to navigate through the recordset, but you can get the
*only* return value of this example using

vMatchedCount = rst.Fields(0)

This is a very clumsy way to approach your particular problem, but you can
see how some of the language works. I ran some tests on an application of
mine that used the dCount function too much, causing the form to take too
long to refresh when moving through the recordset, and then I found that
this shorthand method worked more quickly than the dCount function:

vMatchedCount = CurrentDb.OpenRecordSet("SELECT Count(1) FROM tblReceipt
WHERE Matched=0").Fields(0)

This had a speed benefit over the dCount function, but I have been curious
if there is a problem with creating a single-use reference to a recordset
that (supposedly) vanishes as soon as the statement is executed. If any of
you MVPs read this, maybe you could comment. My code seems to work fine,
but I wonder if this is bad practice. Any thoughts?

**BTW, the "Count(ReceiptID)" part of your SQL statement evaluates and
counts the records where ReceiptID is not Null. Since the chances are about
100% that this is an autonumber field in your table, all the records that
satisfy your WHERE clause will be counted, so you can get a slightly better
result by providing a dummy variable, i.e., Count(1). No evaluation of the
ReceiptID field is necessary. It can be very useful to know how this works
when you want to count records that have entries in specific fields.
Similarly, the dCount function can accept a dummy variable:

vMatchedCount = dCount(1,"tblReceipt","Matched = 0")

Paul Johnson

 

[Paul James]

Thank you so much, Paul, for such a complete and lucid explanation.

I wasn't asking how to open a recordset for this example, because as you
said, DCount() will do just fine. But I'm trying to understand how to use
recordsets, and your clear explanation will be of great help to me.

Thanks for taking the time to provide me with the information.

Paul

 

[John Spencer (MVP)]

By the way, I think that the Rushmore engine is optimized to give fastest
results on count if you use * vice 1. Of course, that is only from my reading
in the past and was when I first started working with Access 97. So it could
have been overtaken by changes in code or I could be mis-remembering.

vMatchedCount = dCount("*","tblReceipt","Matched = 0")

Or using your other method.

vMatchedCount = CurrentDb.OpenRecordSet("SELECT Count(*) FROM tblReceipt WHERE Matched=0").Fields(0)

I've used the above method in the past and have not seen any problems with it to date.

 

[Paul Johnson]

I read in Steven Roman's "Access Database Design & Programming" that using
the "*" wildcard in dCount would load the entire contents of the recordset,
dragging down performance. Of course, all those values aren't used
anywhere, but I bought his argument and shifted to using the dummy "1" when
not checking for non-null field values.

I have never heard of the Rushmore engine, so you're over my head there.
Roman's 2nd edition was published in 1999, 3rd edition in 2002.

Has anyone actually tested these methods, or noticed a difference in speed?

Paul Johnson

 

[Paul James]

In response to your question about whether anyone has tested these methods,
I built a table with just over four million records and tested how long it
would take to run the following DCount() statements:

DCount("Name","tblNamesAndNumbers","Number = 876578785")
DCount("*","tblNamesAndNumbers","Number = 876578785")
DCount("1","tblNamesAndNumbers","Number = 876578785")

The one with "Name" took about 20 seconds to run. The other two, with "*"
and "1" both took under a second. If there was a difference between the
two, I wasn't able to tell, because they both ran so fast.

This was with the Number field being indexed. Unindexed, both the "*" and
"1" seem to take about as long as "Name."

Thanks for your comments and illumination, gentlemen.

Paul P.