use count from query as variable for if statement

[Linda]

I would like to use a count from a select query as a test in an if statement.
The query has an inner join and uses a variable previously defined in the
'having' portion. How can I assign a value to a variable based on the count
from the query? The code below is the relevant portions of where I am at:

Private Sub cmdTest_Click()
' ask for the Dept and then display a report based on that Dept
Dim strDept As String
Dim strFilter As String
Dim strRptName As String
Dim cnt999 As Integer
...............
'open a department selection form
DoCmd.OpenForm "SelectDept", acNormal, , , , acDialog
'assigns the value from the department selection form
strDept = Forms!SelectDept.lstSelectDept.Value
'close the department selection form
DoCmd.Close acForm, "Forms!SelectDept.Form", acSaveNo
................
'check to see if the specified department has any records
'causes an error on the report if there are no records
'show a message box instead stating that there are no records

cnt999 = "SELECT Count(Allowance.sequence) AS CountOfsequence " & _
"FROM Allowance " & _
"INNER JOIN CANs ON Allowance.Can = CANs.can " & _
"GROUP BY CANs.Department " & _
"HAVING (((CANs.Department) = strDept));"
..............
If cnt999 > 0 Then
..............
End If
..........
End Sub

 

[John Spencer (MVP)]

How about the following. This assumes you are working with an mdb and DAO library.

Dim StrSQL as String

StrSQL = "SELECT Count(Allowance.sequence) " & _
"FROM Allowance " & _
"INNER JOIN CANs ON Allowance.Can = CANs.can " & _
"WHERE CANs.Department = """ & strDept & """"

Cnt999 = CurrentDb().OpenRecordset (strSQL).Fields(0)

 

[Linda]

That works. Thanks!

How about the following. This assumes you are working with an mdb and DAO library.

Dim StrSQL as String

StrSQL = "SELECT Count(Allowance.sequence) " & _
"FROM Allowance " & _
"INNER JOIN CANs ON Allowance.Can = CANs.can " & _
"WHERE CANs.Department = """ & strDept & """"

Cnt999 = CurrentDb().OpenRecordset (strSQL).Fields(0)