MS Access - Writing SQL to count for conditions

[JamesKang123]

I am not very familiar with SQL.

I try to count records, with a field "Mon"
which contain "AL" or "UL"

I am not sure is my SQL statement correct.
When I execute the SQL, error message display
"A RunSQL action requires an argument consisting of
an SQL statement."

I am also thinking of using set filter instead of
SQL but not sure which is a preferable method.

Here my code

   Dim dbs1 As DAO.Database
   Dim rst1 As DAO.Recordset
   Dim sSQL As String
 
 
   Set dbs1 = CurrentDb()
   sSQL = "SELECT TblTempAttendance.EmpName, Count(TblTempAttendance.mon) AS MonLeave from TblTempAttendance where TblTempAttendance.Mon  Like '*" & "AL" & "*' OR TblTempAttendance.Mon Like '*" & "UL" & "*' GROUP BY TblTempAttendance.EmpName HAVING TblTempAttendance.empName like '*" & EmpName & "*'"
   DoCmd.RunSQL sSQL

[Rabbit]

RunSQL is for action queries. What you have is a select query. You need to use OpenRecordset for that and assign the result to a recordset variable.

[NeoPa]

As this requires counting of items involved within a GROUP BY clause, and DCount() doesn't handle SQL as its Domain parameter, a recordset does appear to be required. I would consider using SQL as follows though, for simplicity :

Dim strSQL As String
 
strSQL = "SELECT   [EmpName]" & _
         "       , Count(*) AS MonLeave " & _
         "FROM     [TblTempAttendance] " & _
         "WHERE    ([Mon] Like '*{AU}L*')" & _
         "  AND    ([EmpName] Like '*" & Me.EmpName & "*') " & _
         "GROUP BY [EmpName]"

[JamesKang123]

Thanks for the beautiful SQL Statement, I included the OpenRecordset.

It work!!!

 <SQL Statement >
  Set rst1 = CurrentDb.OpenRecordset(SQL)
  result = rst1!MonLeave

[JamesKang123]

Thank for your advise of using OpenRecordset. Now I see the dfferent
between action query and open query.

[NeoPa]

NB. This SQL, as written (to match your original explanation), can return multiple records so you should be aware that your code will return only the first of these.

[JamesKang123]

Yes, it is what I want, Noted with thanks