PC Review


Reply
Thread Tools Rate Thread

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

 
 
Paul James
Guest
Posts: n/a
 
      23rd Sep 2004
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


 
Reply With Quote
 
 
 
 
Dan Artuso
Guest
Posts: n/a
 
      23rd Sep 2004
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"

--
HTH
Dan Artuso, Access MVP


"Paul James" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
 
 
 
Paul James
Guest
Posts: n/a
 
      23rd Sep 2004
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


 
Reply With Quote
 
Paul Johnson
Guest
Posts: n/a
 
      23rd Sep 2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
Paul James
Guest
Posts: n/a
 
      23rd Sep 2004
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


 
Reply With Quote
 
John Spencer (MVP)
Guest
Posts: n/a
 
      23rd Sep 2004
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 James wrote:
>
> 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

 
Reply With Quote
 
Paul Johnson
Guest
Posts: n/a
 
      24th Sep 2004
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


"John Spencer (MVP)" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 James wrote:
> >
> > 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



 
Reply With Quote
 
Paul James
Guest
Posts: n/a
 
      24th Sep 2004
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: OpenRecordset SQL using wrong SQL statement Douglas J. Steele Microsoft Access VBA Modules 1 30th Dec 2008 04:00 PM
Re: OpenRecordset SQL using wrong SQL statement Douglas J. Steele Microsoft Access VBA Modules 0 29th Dec 2008 02:56 PM
run SQL statement from same SQL statement for searching =?Utf-8?B?TWlrZQ==?= Microsoft Access VBA Modules 4 15th Apr 2005 06:47 PM
using the results of a SQL SELECT(COUNT) statement in VBA Paul James Microsoft Access Queries 7 24th Sep 2004 09:44 PM
using the results of a SQL SELECT(COUNT) statement in VBA Paul James Microsoft Access Forms 7 24th Sep 2004 09:44 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:44 PM.