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

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dan Artuso (dartuso_at_NSpagepearls.com)
Date: 09/23/04 

Date: Wed, 22 Sep 2004 20:08:34 -0400

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" <pponzelliBEGONE@SPAMsurewest.FOREVERnet> wrote in message news:10l42lmgcjo3vd7@corp.supernews.com...
> 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
>
>

Relevant Pages

  • Re: using the results of a SQL SELECT(COUNT) statement in VBA
    ... Dan Artuso, Access MVP ... > Here's the SQL statement: ... > get it to assign the result to a variable in VBA. ...
    (microsoft.public.access.queries)
  • Re: using the results of a SQL SELECT(COUNT) statement in VBA
    ... Dan Artuso, Access MVP ... > Here's the SQL statement: ... > get it to assign the result to a variable in VBA. ...
    (microsoft.public.access.formscoding)
  • Re: Scroll through records and get match
    ... Doug Steele, Microsoft Access MVP ... "User-defined type not defined" at Dim dbs As DAO.Database. ... that I posted is a select query. ... cannot use it to open an SQL statement that you generate via code. ...
    (microsoft.public.access.modulesdaovba)
  • Re: RecordCount = -1
    ... There is a problem with your SQL statement. ... Ken Snell ... "jstroup" wrote in message ... >>> <MS ACCESS MVP> ...
    (microsoft.public.access.formscoding)
  • Re: Scroll through records and get match
    ... A RunSQL statemnt requires an argument consisting of an SQL statement. ... "Ken Snell " wrote: ... <MS ACCESS MVP> ... Animal based on todays date. ...
    (microsoft.public.access.modulesdaovba)