Public Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'If Me.Keuzerondje_Actief = -1 Then
' strWhere = strWhere & "([Act] = True) AND "
'End If
If Not IsNull(Me.Kzl_personeelsnummer) Then
strWhere = strWhere & "([personeelsnummer] Like """ & Me.Kzl_personeelsnummer & "*"") AND "
End If
If Not IsNull(Me.Kzl_JaartalStart) Then
strWhere = strWhere & "([Datum start] Like ""*" & Me.Kzl_JaartalStart & "*"") AND "
End If
If Not IsNull(Me.Kzl_MaandkeuzeStart) Then
strWhere = strWhere & "([Datum start] Like ""*" & Me.Kzl_MaandkeuzeStart & "*"") AND "
End If
If Not IsNull(Me.Kzl_JaartalStop) Then
strWhere = strWhere & "([Datum stop] Like ""*" & Me.Kzl_JaartalStop & "*"") AND "
End If
If Not IsNull(Me.Kzl_MaandkeuzeStop) Then
strWhere = strWhere & "([Datum stop] Like ""*" & Me.Kzl_MaandkeuzeStop & "*"") AND "
End If
If Not IsNull(Me.Kzl_Geslacht) Then
strWhere = strWhere & "([Geslacht] Like ""*" & Me.Kzl_Geslacht & "*"") AND "
End If
If Not IsNull(Me.Kzl_afdelingkeuze_algemeen) Then 'is numeriek veld
strWhere = strWhere & "([CAfdeling] = " & Me.Kzl_afdelingkeuze_algemeen & ") AND "
End If
If Not IsNull(Me.Kzl_FTEquivalent) Then 'is numeriek veld
strWhere = strWhere & "([FT Equivalent] = " & Me.Kzl_FTEquivalent & ") AND "
End If
If Not IsNull(Me.Kzl_betrekking) Then 'is numeriek veld
strWhere = strWhere & "([Beklede betrekking] = """ & Me.Kzl_betrekking & """) AND "
End If
If Not IsNull(Me.Kzl_Statuut) Then 'is numeriek veld
strWhere = strWhere & "([Statuut] = """ & Me.Kzl_Statuut & """) AND "
End If
If Not IsNull(Me.Kzl_Tewerkstelling) Then 'is numeriek veld
strWhere = strWhere & "([Tewerkstelling] = """ & Me.Kzl_Tewerkstelling & """) AND "
End If
If Not IsNull(Me.Kzl_Duur) Then 'is numeriek veld
strWhere = strWhere & "([Duur] = """ & Me.Kzl_Duur & """) AND "
End If
'If Not IsNull(Me.Kzl_prioriteit_gemeente) Then
' strWhere = strWhere & "([PrGem] = " & Me.Kzl_prioriteit_gemeente & ") AND "
'End If
'If Not IsNull(Me.Kzl_gemeente) Then
' strWhere = strWhere & "([BVWoonplaats] = """ & Me.Kzl_gemeente & """) AND "
'End If
'If Not IsNull(Me.kzl_categorie) Then
' strWhere = strWhere & "([Categorie] = """ & Me.kzl_categorie & """) AND "
'End If
' If Me.Keuzerondje_KVC = -1 Then
' strWhere = strWhere & "([KVB_wachtlijst] = True) AND "
'End If
' If Me.Keuzerondje_KVC_OK = -1 Then
' strWhere = strWhere & "([KVB_wachtlijst_voldaan] = True) AND "
'End If
'Another text field example. Use Like to find anywhere in the field.
'If Not IsNull(Me.txtFilterMainName) Then
'strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
'End If
'Number field example. Do not add the extra quotes.
'If Not IsNull(Me.cboFilterLevel) Then
'strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ") AND "
'End If
'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
'If Me.cboFilterIsCorporate = -1 Then
' strWhere = strWhere & "([IsCorporate] = True) AND "
'ElseIf Me.cboFilterIsCorporate = 0 Then
' strWhere = strWhere & "([IsCorporate] = False) AND "
'End If
'Date field example. Use the format string to add the # delimiters and get the right international format.
'If Not IsNull(Me.txtStartDate) Then
' strWhere = strWhere & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
'End If
'Another date field example. Use "less than the next day" since this field has times as well as dates.
'If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
' strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") 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."
'cmdReset_Click
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.[Sub_Frm_Personeelsleden].Form.Filter = strWhere
Me.[Sub_Frm_Personeelsleden].Form.FilterOn = True
Me.Txtaantal = Me.Sub_Frm_Personeelsleden.Form.Recordset.RecordCount
End If
End Sub
