Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Sep 28, 2024
Solved

Filter Form and Syntax

Hello Experts,

 

I am trying to filter a form based on a combo box and I need 2 criteria.  I can filter with 1 criteria no issue but adding another criteria that is TEXT is the problem.  

In the below, [ExpiredYN] is a cbo with a row source: "Terminated";"Active". 

[ProjIDfk] is not text. 

 

do you see where I am wrong?  

 

Private Sub cboFilterProj_AfterUpdate()

'http://allenbrowne.com/ser-28.html

If IsNull(Me.cboFilterProj) Then
Me.FilterOn = False
Else
Me.Filter = "ProjIDfk = " & Me!cboFilterProj & "' And [ExpiredYN] = '" & Me!cboStatus & "'"

                                                                         ^dqsq                         ^sqdq                       ^dqsqdq

the above returns a syntax:

 

Me.Filter = "ProjIDfk = " & Me!cboFilterProj & "' And " & [ExpiredYN] = '" & Me!cboStatus & "'"

                                                                          ^dqsq   ^??                      ^sqdq                       ^dqsqdq

I get a compile error on the above:

 

Me.FilterOn = True
End If

End Sub

 

 

thank you

 

 

  • Tony2021 

     

    Please confirm.

     

    In a table, you have a field named [ExpiredYN], correct?

     

    This field, [ExpiredYN], is configured as a combo box with a value list row source. The values in that value list are  "Terminated";"Active", correct?

     

    In that case, the expression posted should work with one small exception. There is an extra single quote shown in red here:  & "' And ...

     

     & "' And [ExpiredYN] = '" & Me!cboStatus & "'"

     

    should be

     & " And [ExpiredYN] = '" & Me!cboStatus & "'"

     

     

     

     

3 Replies

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    Tony2021 

     

    Please confirm.

     

    In a table, you have a field named [ExpiredYN], correct?

     

    This field, [ExpiredYN], is configured as a combo box with a value list row source. The values in that value list are  "Terminated";"Active", correct?

     

    In that case, the expression posted should work with one small exception. There is an extra single quote shown in red here:  & "' And ...

     

     & "' And [ExpiredYN] = '" & Me!cboStatus & "'"

     

    should be

     & " And [ExpiredYN] = '" & Me!cboStatus & "'"

     

     

     

     

    • Tony2021's avatar
      Tony2021
      Iron Contributor
      Hi George, that worked perfectly! thank you very much.

Resources