SQL Output to Variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to do this:

Take a SQL Count query, move the result to to a variable, and then throw the
variable to a msgbox.

I have been trying to do this:

variable = docmd.runSQL "Select Count (*) from TABLE"

However, this doesn't seem to work. Does anyone have any ideas? Thanks.
 
There are at least three ways to do this ...

Public Sub GetCount()

'Using DAO
Dim db As DAO.Database
Dim rstd As DAO.Recordset
Dim lngCountd As Long
Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT Count(*) AS TheCount FROM tblTest")
lngCountd = rstd.Fields("TheCount")
rstd.Close
MsgBox "The count is " & lngCountd, vbOKOnly, "Using DAO"

'Using ADO
Dim rsta As ADODB.Recordset
Dim lngCounta As Long
Set rsta = New ADODB.Recordset
With rsta
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Count(*) AS TheCount FROM tblTest"
.Open
lngCounta = rsta.Fields("TheCount")
.Close
End With
MsgBox "The count is " & lngCounta, vbOKOnly, "Using ADO"

'Using DCount()
MsgBox "The count is " & DCount("*", "tblTest"), vbOKOnly, "Using
DCount()"

End Sub
 
Hi,

In general, you can place the SQL statement as RowSource of a listbox
control, to display many fields, many rows.


Here, with a single value, a single record, you can try:


myVar = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM
table").Fields(0).Value




Hoping it may help,
Vanderghast, Access MVP
 
First, RunSQL only works with Action Queries (Make Table, Append, Delete,
Update)
Second, if you want to do an actiion query, use the CurrenDb.Execute
instead, it is much faster.
Now, here is what you want to do:
variable = DCount("*", "TABLE")
All this will give you is the number of records in the table. If you want
to count for a specific condition:
strStreetName = "Main"
variable = DCount("*", "TABLE","[Street] = '" & strStreetName & "'")
This will give you a count of records for people who live on Main Street.
variable = DCount("*", "TABLE","[Street] Like 'm*'")
This will give you a count of records for people who live on any street name
that starts with m.
 
Back
Top