newbie question about the COUNT and Access

[ernie.bornheimer]

Hello

I have a SQL statement that seems okay:

SELECT COUNT(*)
FROM myTable
WHERE agent_id = myValue

I'll end up with a small integer (maybe zero). What's the quickest way
to get that number into a variable? Do I need to use a recordset, or
is there a way to do it more directly? I am using Access 2003.

Thank you!

Ernie

 

[Douglas J. Steele]

You can use a recordset:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT COUNT(*) As TotalRecords " & _
"FROM myTable " & _
"WHERE agent_id = " & myValue
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
MsgBox "Count = " & rsCurr!TotalRecords
rsCurr.Close
Set rsCurr = Nothing

However, far simpler would be to use the DCount aggregate function:

MsgBox "Count = " & DCount("*", "myTable", "agent_id = " & myValue)

Note that I've assume that agent_id is a numeric field (and that myValue
contains the value you want to look up. If agent_id is text, you'll need to
use

strSQL = "SELECT COUNT(*) As TotalRecords " & _
"FROM myTable " & _
"WHERE agent_id = """ & myValue & """"

or

DCount("*", "myTable", "agent_id = """ & myValue & """")

(that's 3 double quotes in front, 4 double quotes after)

 

[John Spencer]

There is the DCount function in VBA. Assuming AgentID is numeric, you could
use
MyVar = DCount("*","MyTable","AgentID=" & myValue)

If AgentID is a text field then you need to surround my value with quote
marks.
MyVar = DCount("*","MyTable","AgentID=""" & myValue & """")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ernie.bornheimer]

Thank you both very much!!