Allen Browne search code using check boxes

[baronqueefington]

Hello Again,

I am currently using Allen Browne's search code on a form, it working well but now I would like to add 3-4 check boxes to the form which should be used when searching. Here is the code below.


Private Sub cmdSearch_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtSearchName) Then
strWhere = strWhere & "([Name of relevant person] Like ""*" & Me.txtSearchName & "*"") AND "
End If

If Not IsNull(Me.cboAddress) Then
strWhere = strWhere & "([Address] = '" & Me.cboAddress & "') AND "
End If

If Not IsNull(Me.cboBiaName) Then
strWhere = strWhere & "([BIA name (Allocated to)] = '" & Me.cboBiaName & "') AND "
End If

If Not IsNull(Me.cboMhaName) Then
strWhere = strWhere & "([MHA name] = '" & Me.cboMhaName & "') AND "
End If

If Not IsNull(Me.txtDolsSearch) Then
strWhere = strWhere & "([Dol] = " & Me.txtDolsSearch & ") AND "
End If


If Not IsNull(Me.txtAisSearch) Then
strWhere = strWhere & "([AIS Number] = " & Me.txtAisSearch & ") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: 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




I have tried adding:

If Me.chkGranted = True Then
strWhere = strWhere & "([Outcome] = 'Granted') AND "
End If

If Me.chkIn Progress = True Then
strWhere = strWhere & "([Outcome] = 'In Progress') AND "
End If


But it will only allow for one check box to be ticked, ticking more that one causes is it to filter none as I assume it's trying to check 'in progress' and 'Granted' against the same record which won't work.

Using:

If Me.chkGranted = True Then
strWhere = strWhere & "([Outcome] = 'Granted' or [Outcome] = 'In Progress') AND "
End If

allows for the 'outcome' to be filtered by 2 or more criteria's but it still doesn't achieve what I need.

is there a way to loop through this section and compile more than one criteria on the 'outcome' field using check boxes?

Thank you for any help you can provide.

[ssanfu]

Should be able to.....

but now I would like to add 3-4 check boxes to the form which should be used when searching.

You are mixing "ORs" and "ANDs". Can you provide some search examples including the check boxes?

Do you want
1)

([Name of relevant person] Like "*A*") AND ([Address] = 'B') AND ([BIA  name (Allocated to)] = 'C') AND ([MHA name] = 'D') AND ([Dol] = E) AND  ([AIS Number] = 1)
AND ([Outcome] = 'Granted' or [Outcome] = 'In Progress')

or 2)

([Name of relevant person] Like "*A*") AND ([Address] = 'B') AND ([BIA name (Allocated to)] = 'C') AND ([MHA name] = 'D') AND ([Dol] = E) AND ([AIS Number] = 1)
OR ([Outcome] = 'Granted' or [Outcome] = 'In Progress')

[ssanfu]

In a copy of your dB, add the blue code or paste in the entire search code.
Is this close?

Private Sub cmdSearch_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"
    If Not IsNull(Me.txtSearchName) Then
        strWhere = strWhere & "([Name of relevant person] Like ""*" & Me.txtSearchName & "*"") AND "
    End If

    If Not IsNull(Me.cboAddress) Then
        strWhere = strWhere & "([Address] = '" & Me.cboAddress & "') AND "
    End If

    If Not IsNull(Me.cboBiaName) Then
        strWhere = strWhere & "([BIA name (Allocated to)] = '" & Me.cboBiaName & "') AND "
    End If

    If Not IsNull(Me.cboMhaName) Then
        strWhere = strWhere & "([MHA name] = '" & Me.cboMhaName & "') AND "
    End If

    If Not IsNull(Me.txtDolsSearch) Then
        strWhere = strWhere & "([Dol] = " & Me.txtDolsSearch & ") AND "
    End If

    If Not IsNull(Me.txtAisSearch) Then
        strWhere = strWhere & "([AIS Number] = " & Me.txtAisSearch & ") AND "
    End If


    '2 check boxes for 1 field
    If Me.chkGranted = False And Me.chkInProgress = False Then
        'do nothing
    ElseIf Me.chkGranted = True And Me.chkInProgress = False Then
        strWhere = strWhere & "([Outcome] = 'Granted') AND "
    ElseIf Me.chkGranted = False And Me.chkInProgress = True Then
        strWhere = strWhere & "([Outcome] = 'In Progress') AND "
    ElseIf Me.chkGranted = True And Me.chkInProgress = True Then
        strWhere = strWhere & "([Outcome] = 'Granted' or [Outcome] = 'In Progress') AND "
    End If
    
    '************************************************* **********************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '************************************************* **********************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then    'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else    'Yep: 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