Complex filtering

[diegomarino]

hi, i use this procedure to filter various forms

Private Sub BuildFilt(FrmName As String)   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim rsFilt As DAO.Recordset
   Dim FiltStr As String
   Dim Wldcrd As String
   Dim Wldcrd2 As String
   Set db = CurrentDb
   Dim frm As Form
   If FrmName = "lista candidati" Then
      Set frm = Forms!contatti!
[Lista Candidati].Form
   Else
      Set frm = Forms(FrmName)
   End If
   Me.SubFiltri.Form.OrderBy = "[Operatore]"
   Me.SubFiltri.Form.OrderByOn = True
   Set rs = Me.SubFiltri.Form.RecordsetClone
   rs.Filter = "[Active] = true"
   Set rsFilt = rs.OpenRecordset
   rsFilt.MoveFirst
   Do Until rsFilt.EOF
      If IsNull(rsFilt!Espressione) Then
         Select Case rsFilt!TipoCampo
            Case "text", "memo"
               Wldcrd = """"
               Wldcrd2 = """"
            Case "long", "double", "boolean"
               Wldcrd = ""
               Wldcrd2 = ""
            Case "date"
               Wldcrd = "#"
               Wldcrd2 = "#"
            Case Else
               Wldcrd = ""
               Wldcrd2 = ""
         End Select
         Select Case rsFilt!Criterio
            Case "Comincia con"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] LIKE """ & rsFilt!valore1 & "*"" "
            Case "non Comincia con"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] NOT LIKE """ & rsFilt!valore1 & "*"" "
            Case "Finisce con"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] LIKE ""*" & rsFilt!valore1 & """ "
            Case "non Finisce con"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] NOT LIKE ""*" & rsFilt!valore1 & """ "
            Case "contiene"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] LIKE ""*" & rsFilt!valore1 & "*"" "
            Case "non contiene"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] NOT LIKE ""*" & rsFilt!valore1 & "*"" "
            Case "="
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] = " & Wldcrd & rsFilt!valore1 & Wldcrd2 & " "
            Case "<>"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] <> " & Wldcrd & rsFilt!valore1 & Wldcrd2 & " "
            Case ">"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] > " & Wldcrd & rsFilt!valore1 & Wldcrd2 & " "
            Case "<"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] <" & Wldcrd & rsFilt!valore1 & Wldcrd2 & " "
            Case "vuoto"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] is null "
            Case "non vuoto"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] is not null "
            Case "tra"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] Between " & Wldcrd & rsFilt!valore1 & Wldcrd2 & " and " & Wldcrd & rsFilt!Valore2 & Wldcrd2 & " "
            Case "non compreso tra"
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] NOT Between " & Wldcrd & rsFilt!valore1 & Wldcrd2 & " and " & Wldcrd & rsFilt!Valore2 & Wldcrd2 & " "
         End Select
      Else
         FiltStr = FiltStr & rsFilt!Operatore & " (" & rsFilt!Espressione & ") "
      End If
      Debug.Print (FiltStr)
      rsFilt.MoveNext
   Loop


   frm.Filter = FiltStr
   frm.FilterOn = True
   Me.SubFiltri.Form.OrderBy = "[Campo],[criterio]"
   Me.SubFiltri.Form.OrderByOn = True
   rsFilt.Close
   Set rsFilt = Nothing
   Set rs = Nothing
   MinimizeBTN_Click

the part that gives me trouble is this one

            Case "="
               FiltStr = FiltStr & rsFilt!Operatore & " [" & rsFilt!Campo & "] = " & Wldcrd & rsFilt!valore1 & Wldcrd2 & " "

rsFilt!Valore1 is a textbox in my subform (i use form.recordsetclone), where are all the criteria. Usually is a value field, like a number, date, text etc etc and it works fine. My question is: instead of placing a value i'd like to use a control, maybe a combobox in main form.

so, rsFilt!valore1 instead of being a text (maybe) should be something like "forms!filiali!cercacittàCB".

but the filter do not consider the text in the combobox, just set the field i want to filter to "forms!filiali!cercacittàCB", that's not a value i want to search

[June7]

Try Eval() function.

Eval(rsFilt!valore1)

[diegomarino]

that's it!