i'm using a complex system, cause for every field i need a lot of criteria
Code:
Private Sub LKFiltBtn_Click()
DoCmd.RunCommand acCmdSaveRecord
FieldName = " [Nome cognome] "
NameFiltStr = ""
Set db = CurrentDb
Set NameFiltRst = db.OpenRecordset("FiltroLKName")
If NameFiltRst.RecordCount > 0 Then
NameFiltRst.MoveFirst
Do Until NameFiltRst.EOF
If NameFiltStr = "" Then
NameFiltStr = FieldName & NameFiltRst![Operatore] & NameFiltRst![Wildcard1] & NameFiltRst![NameFilt] & NameFiltRst![Wildcard2]
Else
NameFiltStr = NameFiltStr & NameFiltRst![Logico] & FieldName & NameFiltRst![Operatore] & NameFiltRst![Wildcard1] & NameFiltRst![NameFilt] & NameFiltRst![Wildcard2]
End If
NameFiltRst.MoveNext
Loop
End If
TotalFiltStr = NameFiltStr
DoCmd.RunCommand acCmdSaveRecord
FieldPosition = " [Position] "
PositionFiltStr = ""
Set db = CurrentDb
Set PositionFiltRst = db.OpenRecordset("FiltroLKPosition")
If PositionFiltRst.RecordCount > 0 Then
PositionFiltRst.MoveFirst
Do Until PositionFiltRst.EOF
If PositionFiltStr = "" Then
PositionFiltStr = FieldPosition & PositionFiltRst![Operatore] & PositionFiltRst![Wildcard1] & PositionFiltRst![PosizioneFilt] & PositionFiltRst![Wildcard2]
Else
PositionFiltStr = PositionFiltStr & PositionFiltRst![Logico] & FieldPosition & PositionFiltRst![Operatore] & PositionFiltRst![Wildcard1] & PositionFiltRst![PosizioneFilt] & PositionFiltRst![Wildcard2]
End If
PositionFiltRst.MoveNext
Loop
End If
'MsgBox (PositionFiltStr)
If TotalFiltStr = "" Then
TotalFiltStr = PositionFiltStr
Else
If PositionFiltStr <> "" Then
TotalFiltStr = TotalFiltStr & " AND (" & PositionFiltStr & ")"
End If
End If
DoCmd.RunCommand acCmdSaveRecord
FieldCompany = " [Company] "
BancaFiltStr = ""
Set db = CurrentDb
Set BancaFiltRst = db.OpenRecordset("FiltroLKBanca")
If BancaFiltRst.RecordCount > 0 Then
BancaFiltRst.MoveFirst
Do Until BancaFiltRst.EOF
If BancaFiltStr = "" Then
BancaFiltStr = FieldCompany & BancaFiltRst![Operatore] & BancaFiltRst![Wildcard1] & BancaFiltRst![BancaFilt] & BancaFiltRst![Wildcard2]
Else
BancaFiltStr = BancaFiltStr & BancaFiltRst![Logico] & FieldCompany & BancaFiltRst![Operatore] & BancaFiltRst![Wildcard1] & BancaFiltRst![BancaFilt] & BancaFiltRst![Wildcard2]
End If
BancaFiltRst.MoveNext
Loop
End If
If TotalFiltStr = "" Then
TotalFiltStr = BancaFiltStr
Else
If BancaFiltStr <> "" Then
TotalFiltStr = TotalFiltStr & " AND (" & BancaFiltStr & ")"
End If
End If
DoCmd.RunCommand acCmdSaveRecord
FieldCity = " [CittāID] "
CityFiltStr = ""
Set db = CurrentDb
Set CityFiltRst = db.OpenRecordset("FiltroLKCittā")
If CityFiltRst.RecordCount > 0 Then
CityFiltRst.MoveFirst
Do Until CityFiltRst.EOF
If CityFiltStr = "" Then
CityFiltStr = FieldCity & CityFiltRst![Operatore] & CityFiltRst![CittāFilt]
Else
CityFiltStr = CityFiltStr & " " & CityFiltRst![Logico] & FieldCity & CityFiltRst![Operatore] & CityFiltRst![CittāFilt]
End If
CityFiltRst.MoveNext
Loop
End If
If CityFiltStr <> "" And InStr(CityFiltStr, "<>") Then
CityFiltStr = CityFiltStr & " OR [CittāID] is null"
End If
If TotalFiltStr = "" Then
TotalFiltStr = CityFiltStr
Else
If CityFiltStr <> "" Then
TotalFiltStr = TotalFiltStr & " AND (" & CityFiltStr & ")"
End If
End If
DoCmd.RunCommand acCmdSaveRecord
FieldMercato = " [Mercato] "
MercatoFiltStr = ""
Set db = CurrentDb
Set MercatoFiltRst = db.OpenRecordset("FiltroLKMercato")
If MercatoFiltRst.RecordCount > 0 Then
MercatoFiltRst.MoveFirst
Do Until MercatoFiltRst.EOF
If MercatoFiltStr = "" Then
MercatoFiltStr = FieldMercato & MercatoFiltRst![Operatore] & MercatoFiltRst![MercatoFilt]
Else
MercatoFiltStr = MercatoFiltStr & " " & MercatoFiltRst![Logico] & FieldMercato & MercatoFiltRst![Operatore] & MercatoFiltRst![MercatoFilt]
End If
MercatoFiltRst.MoveNext
Loop
End If
If MercatoFiltStr <> "" And InStr(MercatoFiltStr, "<>") Then
MercatoFiltStr = MercatoFiltStr & " OR [Mercato] is null"
End If
If TotalFiltStr = "" Then
TotalFiltStr = MercatoFiltStr
Else
If MercatoFiltStr <> "" Then
TotalFiltStr = TotalFiltStr & " AND (" & MercatoFiltStr & ")"
End If
End If
DoCmd.RunCommand acCmdSaveRecord
FieldSettore = " [Settore] "
SettoreFiltStr = ""
Set db = CurrentDb
Set SettoreFiltRst = db.OpenRecordset("FiltroLKSettore")
If SettoreFiltRst.RecordCount > 0 Then
SettoreFiltRst.MoveFirst
Do Until SettoreFiltRst.EOF
If SettoreFiltStr = "" Then
SettoreFiltStr = FieldSettore & SettoreFiltRst![Operatore] & SettoreFiltRst![SettoreFilt]
Else
SettoreFiltStr = SettoreFiltStr & " " & SettoreFiltRst![Logico] & FieldSettore & SettoreFiltRst![Operatore] & SettoreFiltRst![SettoreFilt]
End If
SettoreFiltRst.MoveNext
Loop
End If
If SettoreFiltStr <> "" And InStr(SettoreFiltStr, "<>") Then
SettoreFiltStr = SettoreFiltStr & " OR [Mercato] is null"
End If
If TotalFiltStr = "" Then
TotalFiltStr = SettoreFiltStr
Else
If SettoreFiltStr <> "" Then
TotalFiltStr = TotalFiltStr & " AND (" & SettoreFiltStr & ")"
End If
End If
FieldData = " [Data Invio] "
DataInvioFiltStr = FieldData & DataInvioOperatore & "#" & Me.DataInvioFilt & "#" & " OR [data invio] is null"
If Me.DataInvioFilt <> "" Then
If TotalFiltStr = "" Then
TotalFiltStr = DataInvioFiltStr
Else
TotalFiltStr = TotalFiltStr & " AND " & "( " & DataInvioFiltStr & ")"
End If
End If
If Not IsNull(Me.InDBInt) Then
If TotalFiltStr = "" Then
TotalFiltStr = "[in db] = " & Me.InDBInt & ""
Else
TotalFiltStr = TotalFiltStr & " AND (" & "[in db] =" & Me.InDBInt & ")"
End If
End If
If Not IsNull(Me.NonInteressanteInt) Then
If TotalFiltStr = "" Then
TotalFiltStr = "[non interessante] = " & Me.NonInteressanteInt & ""
Else
TotalFiltStr = TotalFiltStr & " AND (" & "[non interessante] =" & Me.NonInteressanteInt & ")"
End If
End If
MsgBox (TotalFiltStr)
If TotalFiltStr = "" Then
Forms![lk contatti_messaggiSingle].Form.FilterOn = False
Else
Forms![lk contatti_messaggi].[lk contatti_messaggiSingle].Form.Filter = TotalFiltStr
Forms![lk contatti_messaggi].[lk contatti_messaggiSingle].Form.FilterOn = True
' Forms![lk contatti_messaggiSingle].Filter = TotalFiltStr
' Forms![lk contatti_messaggiSingle].FilterOn = True
End If
End Sub