How to execute SELECT count(*)

lxu · April 5th, 2004, 05:23 PM

Please help and here is the code:

Dim db As DAO.Database
Dim strSQLSel As String

...

strSQLSel = "SELECT Count(*) AS Expr1 FROM TBL-1 WHERE (((TBL-1.[COLUMN-1])='" & var-1 & "') AND ((TBL-1.[COLUMN-2]) Is Not Null)) OR (((TBL-1.[COLUMN-2)='" & var-1 & "'))"

Set db = CurrentDb
db.Execute (strSQLSel)

The error message is "Cannot execute a select query"
If I should not use db.Execute, what should I use here to get the number of count?

Thanks in advance,
Lx

Steven · April 5th, 2004, 08:27 PM

And just where exactly where you wanting this Count to be returned to? A MsgBox? The Debug Window?
the Execute statement can only execute actions, not selects.

Here's two ways you could get around your problem
1: Use a recordset

Code:

strSQLSel = "SELECT Count(*) AS Expr1 FROM TBL-1 WHERE (((TBL-1.[COLUMN-1])='" & var-1 & "') AND ((TBL-1.[COLUMN-2]) Is Not Null)) OR (((TBL-1.[COLUMN-2)='" & var-1 & "'))"

Dim rst as Recordset
rst.MoveFirst
Set rst = CurrentDB.OpenRecordset("strSQLSel")
Debug.Print rst("Expr1")
rst.Close
Set rst = Nothing

2: Create a new query

Code:

Dim qdf as QueryDef
Set qdf = CurrentDB.CreateQueryDef("NewQuery", strSQLSel)
docmd.OpenQuery "NewQuery"

I'm guessing it's the first one you'll end up with

I am a loud man with a very large hat. This means I am in charge

lxu · April 8th, 2004, 08:16 AM

Thanks for the help. It works.

lx