Filter Form using combination of Combo Boxes 1

[troypat]

Using Access 2003;

I'm trying to filter a simple continuous form using selections from various combo boxes.

It is not using any sub-forms.

Using the below code I am getting run time error '2001' when any one of the combo boxes doesn't equal "All".

I'm aware that this error is to do with the parenthesis or syntax of "strFilter", but after spending days of searching the internet and forums, and making every combination of Me. Me! [ """ "'" etc... I simply can't get it to work.


My Code;

Private Sub ApplyFilter_Click()

Dim strFilter As String
    
    strFilter = "1=1"
    
    If Me.QuotedFilter <> "All" Then
      strFilter = strFilter & " AND [Quoted]='" & Me!QuotedFilter & "'"
    End If
    
    If Me.AcceptedFilter <> "All" Then
       strFilter = strFilter & " AND [Accepted]='" & Me!AcceptedFilter & "'"
    End If
    
    If Me.CompletedFilter <> "All" Then
       strFilter = strFilter & " AND [Completed]='" & Me!CompletedFilter & "'"
    End If
    
    Me.Filter = strFilter    
    Me.FilterOn = True
    
End Sub

Any help would be greatly appreciated, I'm sure it's a simple solution!

Thanks in advance,
Troy

 

[troypat]

Also, if it helps:

Quoted, Accepted and Completed are the form's fields

Quoted is of Date format

Accepted and Completed are of Yes/No format

QuotedFilter, AcceptedFilter and CompletedFilter are all unbound combo boxes that have a pre-made list of choices.

 

[troypat]

I have managed to get further with the query by removing the ' from the Yes/No combo values

And so now have a new problem! I now want the Quoted field, in Date format, to be filtered for blanks or non-blanks, but cannot get it to work.

My Code:

Private Sub ApplyFilter_Click()

Dim strFilter As String
    
    strFilter = "1=1"
    
    If Me.QuotedFilter <> "All" Then
        If Me.QuotedFilter = "Quoted" Then
            strFilter = strFilter & " AND (Quoted <> '""') "
        Else
            If Me.QuotedFilter = "Not Quoted" Then
                strFilter = strFilter & " AND (Quoted = '""') "
            End If
        End If
    End If
    
    If Me.AcceptedFilter <> "All" Then
       strFilter = strFilter & " AND Accepted = " & [AcceptedFilter]
    End If
    
    If Me.CompletedFilter <> "All" Then
       strFilter = strFilter & " AND Completed = " & [CompletedFilter]
    End If
    
    If strFilter = "1=1" Then
        Me.FilterOn = False
    Else
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
    
End Sub

Should the Non-Blanks "Quoted" filter look for both zero-length and Null values?

What syntax does this need?

Again, any help or general rules for these things would be greatly appreciated.

Sorry for possibly an awkward thread post.

 

[PHV]

Quoted is of Date format
Replace this:
strFilter = strFilter & " AND (Quoted <> '""') "
with this:
strFilter = strFilter & " AND Not (Quoted Is Null) "

and this:
strFilter = strFilter & " AND (Quoted = '""') "
with this:
strFilter = strFilter & " AND (Quoted Is Null) "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ProgramError]

Assuinf that all your filters have all in them then this is the resulting string using your logic.

"1=1 AND (Quoted <> '""') AND Accepted = All AND Completed = All"

and using the replaced sections provided by PHV

1=1 AND Not (Quoted Is Null) AND Accepted = All AND Completed = All

Is this what you were expecting?
I would have expected speech marks around both All's and I can't understand the reason for the inclusion of the 1=1 as this will always be true.

Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[troypat]

Thanks for your help and quick response PHV, that code works like a treat!

ProgramError, I think the quote marks still appear in the filter string so that it matches the default "All" text in each combo box. If a combo is set to "All" it shouldn't filter that particular column.

I believe the "1=1" statement is needed because each additional statement added to the filter string starts with "And". Without the "1=1" I get a "Run Time Error '2448'". It saves putting "And" at the end of each added statement then removing the last "And" before the Form's FilterOn property is to True.

I think the resulting string is what I was expecting since the filters now work. Although, if there's a more efficient or easier way of doing it I'd love to try it out.