Search Form that sets query criteria and sets Operands

[mainerain]

I am trying to create a form with multiple selectable criteria and selectable Operands.

Combo boxes are used to select criteria (set variable and thus a function) , and List boxes are used to select the Operand (set another variable and another function) between criteria, i.e. AND, OR.
Problem is the query doesn't allow functions to set the Operand type.

Is there a possible work around to set the Operand type in the query?



i.e. in the query I would like to set the criteria: John AND Jim; John OR Jim; John AND Jim OR Jane AND Jennifer

Thanks,

[Gicu]

Have a look at a pop-up form I use to apply custom filters on a calling form.

Cheers,
Vlad

[mainerain]

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()

  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