Error with filtering subform using 2 combo boxes

[dharmacloud]

Hi all,

I have made a form with 2 comboboxes: Year and Term to filter the results for a subform containing student courses. It seems to filter the year without any trouble but when I go to Term combobox and choose the term, it gives me this error message: Run-time error ‘3075’: Syntax error in string in query expression ‘[CourseYear] = 2010 AND [Term]= Spring”

Can anyone help me figure out what's wrong with my code? Attached is screenshot of my form/subform. Here's the code, that I'm using:

Private Function SetSubFormFilter()

   Dim mFilter As String
   mFilter = ""

   If Not IsNull(Me.Combo180) Then
      mFilter = " [CourseYear] = " & Me.Combo180.Value
   End If

   If Not IsNull(Me.Combo182) Then
     If Len(mFilter) > 0 Then mFilter = mFilter & " AND "
     mFilter = mFilter & "[Term]= " & Me.Combo182 & "'"
   End If

     If Len(mFilter) > 0 Then
       Me.TestCourseRegistration.Form.Filter = mFilter
       Me.TestCourseRegistration.Form.FilterOn = True
   Else
       Me.TestCourseRegistration.Form.FilterOn = False
   End If

   Me.TestCourseRegistration.Form.Requery
   DoEvents

End Function

Private Sub Command184_Click()
   Me.TestCourseRegistration.Form.FilterOn = False
   Me.TestCourseRegistration.Form.Requery
   DoEvents
End Sub

[pbaldy]

Try

mFilter = mFilter & "[Term]= '" & Me.Combo182 & "'"

[Robeen]

Just looking at your error message, I think this:
‘[CourseYear] = 2010 AND [Term]= Spring”

. . . should be interpreted by Access to read:
[CourseYear] = 2010 AND [Term]= "Spring”

The problem lies generally in how you've constructed your filter string and, specifically, in not passing the quotation marks required for the string that represents Term.

I hope this points you in the right direction.

You have to pass the contents of Combo182 couched in double quotation marks.

Let us know if that helps.

[dharmacloud]

Thanks so so much Paul! I copied and paste your code and it worked!!! You're the greatest!

[pbaldy]

Happy to help!