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

Discussion in 'Microsoft Access Form Coding' started by Paul James, Sep 23, 2004.

  1. Paul James

    Paul James Guest

    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
     
    Paul James, Sep 23, 2004
    #1
    1. Advertisements

  2. Paul James

    Dan Artuso Guest

    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 news:...
    > 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, Sep 23, 2004
    #2
    1. Advertisements

  3. Paul James

    Paul James Guest

    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 James, Sep 23, 2004
    #3
  4. Paul James

    Paul Johnson Guest

    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
    news:...
    > 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, Sep 23, 2004
    #4
  5. Paul James

    Paul James Guest

    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
     
    Paul James, Sep 23, 2004
    #5
  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
     
    John Spencer (MVP), Sep 23, 2004
    #6
  7. Paul James

    Paul Johnson Guest

    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
    news:...
    > 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
     
    Paul Johnson, Sep 24, 2004
    #7
  8. Paul James

    Paul James Guest

    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.
     
    Paul James, Sep 24, 2004
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Scott

    Exporting the results of a SQL statement made on a form

    Scott, Aug 19, 2003, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    186
    Glen Appleton
    Aug 19, 2003
  2. Bill Morgan

    Execute SQL SELECT statement with VBA code

    Bill Morgan, Apr 11, 2004, in forum: Microsoft Access Form Coding
    Replies:
    26
    Views:
    1,080
    Ken Snell
    Apr 22, 2004
  3. Guest

    Using a text box in the Select Statement of SQL

    Guest, Nov 7, 2005, in forum: Microsoft Access Form Coding
    Replies:
    12
    Views:
    410
    Guest
    Nov 9, 2005
  4. sherriross81

    Using Returned vba SQL Results

    sherriross81, Oct 22, 2008, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    153
    Dale Fye
    Oct 22, 2008
  5. Paul

    changing the syntax of an SQL Select statement to appease VBA

    Paul, Mar 19, 2009, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    748
Loading...

Share This Page