Test if SQL SELECT Query is empty 1

[hcisteve]

I want to be able to do one action in my VBA code if my SQL SELECT query is NULL and another one if it isn't. To do this, I need know how to test my VBA SELECT code to determine if the SELECT statement is NULL. The code I want to test is:


SQL1 = "Select ID"
SQL1 = SQL1 + " FROM MyTable"
SQL1 = SQL1 + " Where ((MyTable.FirstSurvey <> Yes))"

CurrentDb.OpenRecordset SQL1

Steve

 

[r937]

if you don't actually need the ID numbers, you can use

select count(*) as howmany
  from MyTable
 where MyTable.FirstSurvey <> Yes

and the howmany column will contain 0 or some number


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[PHV]

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset SQL1
If rs.BOF Or rs.EOF Then
MsgBox SQL1 & " is empty"
Else
MsgBox SQL1 & " returns " & rs(0)
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hcisteve]

It works!! Thanks!!

Steve