If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
using the results of a SQL SELECT(COUNT) statement in VBA
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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)" wrote in message ... 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 |
#8
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
using the results of a SQL SELECT(COUNT) statement in VBA | Paul James | Using Forms | 7 | September 24th, 2004 09:44 PM |
return true in sql statement | Joanne | Running & Setting Up Queries | 2 | September 18th, 2004 10:20 PM |
Is it possible to create a sql statement for this ? | Fie Fie Niles | General Discussion | 6 | August 29th, 2004 11:30 AM |
Function isn't available in expressions in query expression | Reiner Harmgardt | General Discussion | 4 | July 21st, 2004 09:30 AM |
Access 2000 query SQL statement into VBA code | Clint | Running & Setting Up Queries | 1 | June 10th, 2004 01:33 PM |