dlookup and sql

[Johnny C.]

I'm looking for an easier, more elegant method...I have code in a form that
creates a temp query using sql, then using dlookup to get data, eg. using sql
to return a count of records that meet a specific criteria from a table, then
using dlookup to return the count. eg:

Dim qry As QueryDef
Set qry = New QueryDef

With qry
.Name = "tempqry"
.sql = "SELECT Count(LastName) as CountLastName, LastName " & _
"FROM Staff " & _
"GROUP BY LastName " & _
"WHERE LastName = 'Smith';"
End With

then later

dim x as integer

x = DLOOKUP("[CountLastName]","[tempqry]","[LastName] = 'Smith'")

Is there a more elegant way to pass the result from a sql statement straight
into a variable?

 

[John Spencer (MVP)]

well, you might create a recordset based on your query and then grab the value
from there.

UNTESTED AIRCODE snippet (in other words - watch out for errors

Dim rstAny as DAO.Recordset
Dim strSQL as String
Dim x as Integer

strSQL ="SELECT Count(LastName) as CountLastName, LastName " & _
"FROM Staff " & _
"GROUP BY LastName " & _
"WHERE LastName = 'Smith';"

Set rstAny = CurrentDb().OpenRecordset(strSQL)
x = rstAny.Fields("CountLastName")

Set rstany = Nothing
rstAny.Close

Although with your specific example, you could just use the DCount function and
not bother with a query or Dlookup or recordsets. As in the following.

X = DCount("LastName","Staff","LastName=" & Chr(34) & "Smith" & Chr(34))