View Single Post
Old April 5th, 2004, 08:27 PM
Steven Steven is offline
Friend of Wrox
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts

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
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
Set rst = CurrentDB.OpenRecordset("strSQLSel")
Debug.Print rst("Expr1")
Set rst = Nothing
2: Create a new query
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