Access SQL

SELECT is missing in the query.

Syntax error

Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE (((tblUserMaster.Active = 'Yes') And ((tblUserMaster.OpDiv) = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Select allOpen in new window

Always count an opening and closing parenthesis. In this query you have 5 opening and only 4 closing. Try to remove first one:

Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE ((tblUserMaster.Active = 'Yes') And ((tblUserMaster.OpDiv) = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Select allOpen in new window

You can also remove extra pair from last part of where:

Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE (((tblUserMaster.Active = 'Yes') And (tblUserMaster.OpDiv = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Select allOpen in new window

Set rst = CurrentDb.OpenRecordset("Select tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.[Lname], tblUserMaster.[Level], tblUserMaster.[Approver] From tblUserMaster WHERE (((tblUserMaster.Active = 'Yes') And (tblUserMaster.OpDiv = 'CDC')) GROUP BY tblUserMaster.[OpDiv], tblUserMaster.[Fname], tblUserMaster.Lname, tblUserMaster.[Level], tblUserMaster.[Approver] ORDER BY tblUserMaster.[Level]")

Select allOpen in new window

It is very hard to debug a long SQL statement in a line of code -- I recommend saving it as a temporary query, which is then used to create a recordset.  That makes it much easier to examine and correct any errors in the SQL.  Here are some examples (using the CreateAndTestQuery procedure at the of the code):

Private Sub cmdFilter_Click()
'Created by Helen Feddema 10-Aug-2010
'Last modified by Helen Feddema 10-Aug-2010

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lngCount As Long
   Dim lst As Access.ListBox
   Dim strFilter As String
   Dim strData As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strSQL As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   
   strFilter = ""
   Set lst = Me![lstStates]
      
   'Check that at least one state has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one state"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   
   'Create concatenated filter string
   For Each varItem In lst.ItemsSelected
      strData = Nz(lst.Column(0, varItem))
      strFilter = strFilter & "[LocationState] = " & Chr(39) _
         & strData & Chr(39) & " Or "
   Next varItem

   'Trim last "Or" from filter string
   strFilter = Left(strFilter, Len(strFilter) - 4)
   Debug.Print "Filter string: " & strFilter
   
   'Create filtered recordset for other subform
   strQuery = "qryFilteredProjects"
   Set dbs = CurrentDb
   strSQL = "SELECT * FROM tblProjects WHERE " & strFilter _
      & " ORDER BY [LocationState];"
   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   Parent![subFilteredProjects].Form.RecordSource = strQuery
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Select allOpen in new window

Now you should check type of data in two fields:
tblUserMaster.Active = 'Yes' (May be it is boolean field?)
and
tblUserMaster.OpDiv = 'CDC' (Is it text field?)

If first field is boolean (Yes/No field is boolean), you can use:
tblUserMaster.Active = True

It will greatly help if you upload a sample database demonstrating the issue.

Yes, I think we need to see the field names and data types.

I figured out.... see code below.

Set rst = CurrentDb.OpenRecordset("SELECT tblUserMaster.OpDiv, tblUserMaster.Fname, tblUserMaster.Lname, tblUserMaster.Level, tblUserMaster.Approver FROM tblUserMaster WHERE (((tblUserMaster.Active) = Yes) And ((tblUserMaster.OpDiv) = 'CDC')) GROUP BY tblUserMaster.OpDiv, tblUserMaster.Fname, tblUserMaster.Lname, tblUserMaster.Level, tblUserMaster.Approver")

Select allOpen in new window