Filter using multiple combo boxes

[DJRhino1175]

Here is what I have for each combobox:

Private Sub CmbLineSearch_AfterUpdate()

    DoCmd.ApplyFilter , "[LineNoID] = " & Str(Nz([Screen].[ActiveControl], 0))

End Sub

Private Sub cmbSearchDate_AfterUpdate()

    DoCmd.ApplyFilter , "[LineDate] = " & "#" & Format([Screen].[ActiveControl], "mm/dd/yyyy") & "#"

End Sub

Private Sub cmbShiftSearch_AfterUpdate()

    DoCmd.ApplyFilter , "[Shift] = " & Str(Nz([Screen].[ActiveControl], 0))

End Sub

How would I do it so I can filter using any combination of the three or two or just the one using a command button?

I found this on the net and modified for my needs but i get an error:

Private Sub ApplyFilter_Click()

Dim strFilter As String

strFilter = ""

    ' see if there is data in combo box CmbLineSearch, if so add it to the filter
    If Me!cmbSearchDate & vbNullStr <> vbNullStr Then
        strFilter = strFilter & " AND [LineDate] = " & Me.cmbSearchDate
    End If

    If Me!CmbLineSearch & vbNullStr <> vbNullStr Then

       strFilter = strFilter & " AND [LineNoID] = #" & Me.CmbLineSearch & "#"

    End If

    If Me!cmbShiftSearch & vbNullStr <> vbNullStr Then

       strFilter = strFilter & " AND [Shift] = #" & Me.cmbShiftSearch & "#"

    End If

    If strFilter <> "" Then

       ' trim off leading "AND"

       Me.Filter = Mid(strFilter, 4)
       Me.FilterOn = True

    Else

   Me.Filter = ""
   Me.FilterOn = False

    End If

End Sub

The Error is Variable not defined

It highlights vbNullstr

The site I got it from did not have it called out so I have no clue what it's for or if I even need it.

Thanks for any and all advise

[twinnyfo]

DJ,

This is another case of code (at least the source where you got it) over complexifying things just to make it less simple.

Your If...Thens should be:

If Not IsNull(Me!cmbSearchDate) Then

And so on. This could be tricky, because it looks like you are using a combo box, and many times combo boxes have a default value of 0 (at least if you build them that way) and it looks even more like you have very strange values being assigned to Combo Boxes (dates, etc.). Combo boxes usually refer to an index, which also make creating filters much easier.

I think this will get you started in the right direction, but there are certainly more things that may need to be addressed than just this solution.

Hope this hepps.

[DJRhino1175]

These comboboxes are unbound and is only pulling data from what is entered. I have used the individual filters with no issues at this point, but you never know. Thanks for the direction, I will try this as soon as I can, if I have have any questions I will let you know.

[twinnyfo]

DJRhino1175:
These comboboxes are unbound and is only pulling data from what is entered.

Got it--but from a design perspective, I think you may be missing something with how these combo boxes get their data. This is an aside issue, but one of hte goals of this forum is not "just give solutions" but to guide toward better design. Hope that makes sense.

[DJRhino1175]

Private Sub ApplyFilter_Click()

    If Not IsNull(Me!cmbSearchDate) Then
    DoCmd.ApplyFilter , "[LineDate] = " & "#" & Format([Screen].[ActiveControl], "mm/dd/yyyy") & "#"
    End If
    
    If Not IsNull(Me!CmbLineSearch) Then
    DoCmd.ApplyFilter , "[LineNoID] = " & Str(Nz([Screen].[ActiveControl], 0))
    End If
    
    If Not IsNull(Me!cmbShiftSearch) Then
    DoCmd.ApplyFilter , "[Shift] = " & Str(Nz([Screen].[ActiveControl], 0))
    
    End If

End Sub

It throughs an error on the apply filter section. I know that code works as I was able to do them individually. So I think I have it structured wrong.

I " ' " the code for the comboboxes to perform them as a group per the above code.

The error I get is Run-time error 438 "Object doesn't support this property or method."

[DJRhino1175]

Twinny, I understand what your saying. I followed a procedure posted by microsoft on how to filter per a combobox.

SELECT [QryLine Calc].LineDate FROM [QryLine Calc] GROUP BY [QryLine Calc].LineDate ORDER BY [QryLine Calc].LineDate;

Here is what I have for a row source for one of the combo's.

[twinnyfo]

I prefer to filter a form is to use the Form's Filter and FilterOn Settings, as it is more explicit (but you could use the ApplyFilter Method as well):

This should get you more closer:

Private Sub ApplyFilter_Click()
    Dim strFilter As String

    strFilter = ""
    If Not IsNull(Me.cmbSearchDate) Then
        strFilter = _
            " AND [LineDate] = " & _
            "#" & Format(Me.cmbSearchDate, "yyyy-mm-dd") & "#"
    End If

    If Not IsNull(Me.CmbLineSearch) Then
        strFilter = _
            " AND [LineNoID] = " & _
            Me.CmbLineSearch
    End If

    If Not IsNull(Me.cmbShiftSearch) Then
        strFilter = _
            " AND [Shift] = " & _
            Me.cmbShiftSearch
    End If

    Debug.Print Mid(strFilter, 6)

    With Me.Form
        .Filter = Mid(strFilter, 6)
        .FilterOn = (Not strFilter = "")
    End With

End Sub

Again, I don't know if you are dealing with strings, numbers or all dates in your Combo Boxes--So this code might not work just yet. However, since we are building a String, I've added a line to print the string in your immediate window, which will hepp in troubleshooting (always a good practice).

Hope this hepps!

[DJRhino1175]

Twinny,

2 combo boxes are numbers and one combo box is dates.

cmbSearchDate is a date field
cmbLinesearch is a number
cmbShiftSearch is a number

Private Sub ApplyFilter_Click()

    Dim strFilter As String

    If Not IsNull(Me!cmbSearchDate) Then
    
     strFilter = " AND [LineDate] = ""#" & _
        Format(Me.cmbSearchDate, "mm/dd/yyyy") & "#"
     
    End If
 
    If Not IsNull(Me.CmbLineSearch) Then
    
        strFilter = " AND [LineNoID]" _
            = "Me.CmbLineSearch"
        
    End If
 
    If Not IsNull(Me.cmbShiftSearch) Then
    
        strFilter = " AND [Shift]" _
            = "Me.cmbShiftSearch"
        
    End If
 
    Debug.Print Mid(strFilter, 6)
 
    With Me.Form
        .Filter = Mid(strFilter, 6)
        .FilterOn = (Not strFilter = "")
    End With
 
End Sub

Code compiles but nothing happens when I click the button. I did a compact and repair and tried again and nothing happens.

[twinnyfo]

I corrected my code to include the ampersands (&)--which should be something you look for during your own troubleshooting and fixed the date format to the internationally recognized date format. It's impossible for me to troubleshoot this code from long distance. However, the quotes around the arguments should have indicated something to you when you saw it.

Please copy and paste the code above and try again.

[DJRhino1175]

Sorry Twinny, I do not know enough to know when something needs an & or " or ' in it to even trouble shoot for it.

The code as it is now works almost. I think it needs to be looped, but that's just a guess. If I put just the Date it finds it, if I just put the lineno I want it works and so on. But if I put all 3 in it only searches the last one.

So I'm going to search the loop procedure for something like this. I have a couple of DB's that have loops in them, so I'll look at them to see how its structured.

[DJRhino1175]

I found an Allen Brown example I'm going to try tomorrow. Just looking at it we are close, just need to add some and's after each string, but I'm not 100% clear on it at this point.

Here's the link:http://allenbrowne.com/ser-62code.html

[twinnyfo]

I know the problem - this is the challenge of working long distance.

Here (notice lines 13 and 20):

Private Sub ApplyFilter_Click()
    Dim strFilter As String

    strFilter = ""
    If Not IsNull(Me.cmbSearchDate) Then
        strFilter = _
            " AND [LineDate] = " & _
            "#" & Format(Me.cmbSearchDate, "yyyy-mm-dd") & "#"
    End If

    If Not IsNull(Me.CmbLineSearch) Then
        strFilter = _
            strFilter & _
            " AND [LineNoID] = " & _
            Me.CmbLineSearch
    End If

    If Not IsNull(Me.cmbShiftSearch) Then
        strFilter = _
            strFilter & _
            " AND [Shift] = " & _
            Me.cmbShiftSearch
    End If

    Debug.Print Mid(strFilter, 6)
 
    With Me.Form
        .Filter = Mid(strFilter, 6)
        .FilterOn = (Not strFilter = "")
    End With

End Sub

[DJRhino1175]

That work perfectly. I appreciate all your help. Now I need to go debug another issue I'm having.

Thanks again you are so awesome for helping me.

[twinnyfo]

Anytime, DJ!

Glad we could be of service.