Help with multi-field filter in split form (1 Viewer)

[stevekos07]

I want to set up a multi-field filter based on three combobox field selections. I can do this normally, but this one is a bit differend.

Two of the fields are based on selecting True/False fields with names corresponding to the combobox selection, with the criteria as "True", as in the following codes:

Private Sub cboFilterCategory_AfterUpdate()
    With Me
        .Filter = Me!cboFilterCategory & " = True"
        .FilterOn = True
    End With
End Sub

And

Private Sub cboWeekDay_AfterUpdate()
    With Me
        .Filter = Me.cboWeekDay & " = True"
        .FilterOn = True
    End With
End Sub

The third combobox is simple, with a "normal" selection criteria:

Private Sub cboFilterHub_AfterUpdate()
    DoCmd.ApplyFilter "", "[HubLocation]=[Forms]![frmVolunteersSplit]![cboFilterHub]", ""
End Sub

The question is, how to I write the code to filter on these three fields?

I only want filtering on "Not Null" fields with empty fields returning all records for that field.

Thanks in advance for your assistance.

 

[June7]

So you need to concatenate the 3 criteria into a single WHERE phrase? For example review http://allenbrowne.com/ser-62.html

 

[pbaldy]

Create a function that tests each and builds up a filter string, and call it from each one. Here's a start:

If Len(Me!cboFilterCategory & vbNullString) > 0 Then
   strFilter = Me!cboFilterCategory & " = True AND "
End If

 

[stevekos07]

I see. Yes that makes sense. I'll have a go at that and see how it works. Cheers.

 

[stevekos07]

Ok, I have had an initial try at this. Obviously I am way off the mark as far as getting the coding right . I can write some VBA but I am still learning, so some patient guidance is gratefully received.

Each of the filters works fine on their own, but it's concatenating and building the filter string that is the headache for me.

I can build a concatenated filter for simple fields but it is the "With Me... .Filter etc." bit that I don't fully understand how to incorporate.

Obviously the user can use the datasheet section to manually build a multi-filter, but I would like to provide a quicker and simple method.

Private Sub cmdFilterMulti_Click()
    Dim strWhere As String
    Dim lngLen As Long
    
    If Len(Me!cboFilterCategory & vbNullString) <> "" Then
    strWhere = strWhere & "Me!cboFilterCategory & " = True And ""
    End If
    
    If Len(Me!cboWeekDay & vbNullString) > 0 Then
    strWhere = strWhere & "(With Me"
        .Filter = Me.cboWeekDay & " = True) And"
        .FilterOn = True
    End With
    End If
 
    If Len(Me!cboFilterHub & vbNullString) > 0 Then
    strWhere = strWhere & "[HubName]= Me!cboFilterHub & "
    End If
End Sub

 

[Pat Hartman]

You're got the filter code in the wrong place, Move it to after the last If and add another IF to determine if filter string is empty. If it is, don't set the filter.

However, I recommend not using filters at all. This is a technique that essentially locks you into Jet/ACE. If you ever have the need to upsize, you will need to get rid of all the filter code and instead use code that modifies the WHERE clause in your query. Using filters when the BE is not Jet/ACE is very inefficient and pretty much eliminates the benefit of upsizing since all the filtering is done locally and that means that all the data must be downloaded to memory on your PC.

 

[GinaWhipp]

Hmm, you are close. Have a look at...
http://allenbrowne.com/ser-62code.html

 

[stevekos07]

Hmm, you are close. Have a look at...
http://allenbrowne.com/ser-62code.html

Yes, I have that code example but I don't really "get" it as far as how to incorporate all of the criteria. I do know another way to do it, which is not to use the combobox to select the weekday fields, but to display them as checkboxes and build the Where in detail manually using multiple "if" statements. This is cumbersome but it will work and will be still quite efficient for a user to use.

 

[GinaWhipp]

Well, if you ever get the urge it does have a sample download you could study. It is quite flexible and allows for all sorts of searching\filtering of a Form (and can be used to filter reports).

 

[pbaldy]

To address your question, your code would look more like:

    If Len(Me!cboFilterCategory & vbNullString) > 0 Then
      strWhere = strWhere & Me!cboFilterCategory & " = True And "
    End If
    
    If Len(Me!cboWeekDay & vbNullString) > 0 Then
      strWhere = strWhere & Me.cboWeekDay & " = True And "
    End If
 
    If Len(Me!cboFilterHub & vbNullString) > 0 Then
      strWhere = strWhere & "[HubName] = '" & Me!cboFilterHub & "' And "
    End If

Then after that you trim the last 5 characters off the end to get rid of the last "And", since you don't know in advance which criteria will be used.

 

[stevekos07]

Thanks Paul, I'll try that.

 

[pbaldy]

No problem. Allen's stuff is good, but I thought it would be educational to get yours fixed.

 

[stevekos07]

Ok, I think I'm close. Maybe just a syntax error. This is now my code:

Private Sub cmdFilterMulti_Click()
    Dim strWhere As String
    Dim lngLen As Long
    
    If Len(Me!cboFilterCategory & vbNullString) > 0 Then
      strWhere = strWhere & Me!cboFilterCategory & " = True And "
    End If
    
    If Len(Me!cboWeekDay & vbNullString) > 0 Then
      strWhere = strWhere & Me.cboWeekDay & " = True And "
    End If
 
    If Len(Me!cboFilterHub & vbNullString) > 0 Then
      strWhere = strWhere & "[HubName] = '" & Me!cboFilterHub & "'And"
   
    End If
    
'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'There was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'There is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate  Window (Ctrl+G).
        Debug.Print strWhere
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

And this is what displays in the Immediate window:

SafetyCalls = True And Mon = True And [HubName] = 'RCH

The filter string is obviously building, but the syntax needs fixing I think. The code hangs on the line:

Me.Filter = strWhere

 

[pbaldy]

You can see from the Immediate window that you are missing the closing quote after RCH. See a difference between mine and yours (and note that you drop 5 characters)? Mine/yours:

strWhere = strWhere & "[HubName] = '" & Me!cboFilterHub & "' And "
strWhere = strWhere & "[HubName] = '" & Me!cboFilterHub & "'And"

 

[stevekos07]

Ah! Actually it works. I just needed to add a space to the last And " (I think!) Anyway it words now. Thanks heaps everyone!

 

[pbaldy]

Happy to help!