Thanks Gicu for the response. Been to busy. I looked at your code and some of it looks new and unfamiliar to me.
For now I chose to use functions in the query. It's a start. I have global string variables setting global functions. Variables are set with list boxes and combo boxes.
In the query under Criteria: Like gfncPersonnelAnd1() And Like gfncPersonnelAnd2() And Like gfncPersonnelAnd3()
In the query under or: Like gfncPersonnelOr1() Or Like gfncPersonnelOr2() Or Like gfncPersonnelOr3()
Code:
Private Sub btnSearch_Click()
On Error Resume Next
' One Selection
If Not IsNull(cbxPersonnel1) _
And IsNull(lbxPersonnel1.Value) _
And IsNull(cbxPersonnel2) _
And IsNull(lbxPersonnel2.Value) _
And IsNull(cbxPersonnel3) Then
gstrPersonnelAnd1 = ""
gstrPersonnelAnd2 = ""
gstrPersonnelAnd3 = ""
gstrPersonnelOr1 = "*" & cbxPersonnel1 & "*"
gstrPersonnelOr2 = ""
gstrPersonnelOr3 = ""
' Two Selections with AND
ElseIf Not IsNull(cbxPersonnel1) _
And lbxPersonnel1.Value = "AND" _
And Not IsNull(cbxPersonnel2) _
And IsNull(lbxPersonnel2.Value) _
And IsNull(cbxPersonnel3) Then
gstrPersonnelAnd1 = "*" & cbxPersonnel1 & "*"
gstrPersonnelAnd2 = "*" & cbxPersonnel2 & "*"
gstrPersonnelAnd3 = "*" 'this * is needed or this "Two Selection with AND" logic won't work
gstrPersonnelOr1 = ""
gstrPersonnelOr2 = ""
gstrPersonnelOr3 = ""
' Two Selections with OR
ElseIf Not IsNull(cbxPersonnel1) _
And lbxPersonnel1.Value = "OR" _
And Not IsNull(cbxPersonnel2) _
And IsNull(lbxPersonnel2.Value) _
And IsNull(cbxPersonnel3) Then
gstrPersonnelAnd1 = ""
gstrPersonnelAnd2 = ""
gstrPersonnelAnd3 = ""
gstrPersonnelOr1 = "*" & cbxPersonnel1 & "*"
gstrPersonnelOr2 = "*" & cbxPersonnel2 & "*"
gstrPersonnelOr3 = ""
' Three Selections with AND , AND
ElseIf Not IsNull(cbxPersonnel1) _
And lbxPersonnel1.Value = "AND" _
And Not IsNull(cbxPersonnel2) _
And lbxPersonnel2.Value = "AND" _
And Not IsNull(cbxPersonnel3) Then
gstrPersonnelAnd1 = "*" & cbxPersonnel1 & "*"
gstrPersonnelAnd2 = "*" & cbxPersonnel2 & "*"
gstrPersonnelAnd3 = "*" & cbxPersonnel3 & "*"
gstrPersonnelOr1 = ""
gstrPersonnelOr2 = ""
gstrPersonnelOr3 = ""
' Three Selections with OR , OR
ElseIf Not IsNull(cbxPersonnel1) _
And lbxPersonnel1.Value = "OR" _
And Not IsNull(cbxPersonnel2) _
And lbxPersonnel2.Value = "OR" _
And Not IsNull(cbxPersonnel3) Then
gstrPersonnelAnd1 = ""
gstrPersonnelAnd2 = ""
gstrPersonnelAnd3 = ""
gstrPersonnelOr1 = "*" & cbxPersonnel1 & "*"
gstrPersonnelOr2 = "*" & cbxPersonnel2 & "*"
gstrPersonnelOr3 = "*" & cbxPersonnel3 & "*"
' Three Selections with AND , OR
ElseIf Not IsNull(cbxPersonnel1) _
And lbxPersonnel1.Value = "AND" _
And Not IsNull(cbxPersonnel2) _
And lbxPersonnel2.Value = "OR" _
And Not IsNull(cbxPersonnel3) Then
gstrPersonnelAnd1 = "*" & cbxPersonnel1 & "*"
gstrPersonnelAnd2 = "*" & cbxPersonnel2 & "*"
gstrPersonnelAnd3 = "*" 'this * is needed or this "Three Selection with AND , OR" logic won't work
gstrPersonnelOr1 = ""
gstrPersonnelOr2 = ""
gstrPersonnelOr3 = "*" & cbxPersonnel3 & "*"
' Three Selections with OR , AND
ElseIf Not IsNull(cbxPersonnel1) _
And lbxPersonnel1.Value = "OR" _
And Not IsNull(cbxPersonnel2) _
And lbxPersonnel2.Value = "AND" _
And Not IsNull(cbxPersonnel3) Then
gstrPersonnelAnd1 = "*" 'this * is needed or this "Three Selection with OR , AND" logic won't work
gstrPersonnelAnd2 = "*" & cbxPersonnel2 & "*"
gstrPersonnelAnd3 = "*" & cbxPersonnel2 & "*"
gstrPersonnelOr1 = "*" & cbxPersonnel1 & "*"
gstrPersonnelOr2 = ""
gstrPersonnelOr3 = ""
End If
DoCmd.OpenReport "rptSearchEvents", acViewReport
End Sub