Hey all,
I'm using Allen brownes Criteria creator for forms for dynamic filters.
If you haven't seen it, it pretty much constructs a where clause from your txt data on the form (filters).
This is my code:
Code:
Private Sub ReFilterSubForm()
'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 = "dd/mm/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.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
'Check Claim Number LIKE
If Me.txtInsComp <> "" Then
strwhere = strwhere & "[InsCompName] Like ""*" & Me.txtInsComp & "*"" AND "
End If
'Check Full Name LIKE
If Me.txtDate <> "" Then
strwhere = strwhere & "[PayDate] = #" & Me.txtDate & "# AND "
End If
'Check Employer Name LIKE
If Me.txtPayAmt <> "" Then
strwhere = strwhere & "[TotalPaymentValue] = " & Me.txtPayAmt & " 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." - Removed as i dont need it to tell me no filter
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.Filter = strwhere
Me.FilterOn = True
End If
End Sub
Now it works for my first two filters but NOT when i use my date filter.
Therefore I know the rest of the code is ok, but the date part is not!
I am starting to use now() instead of date when making the records, however even the date() records arent working with the filter.
It returns 0 records.
Any ideas?
Any ideas??