Filter for Dates and/or text is same statement

[Chris K]

I'm using one combo (cboFilters) so the user can filter my form by either
choosing preset/sql filters from dropdown or just typing in date or text

It's all working fine except inconstant error - dont expect anyone to error
check this routine but I'm wondering if it can be simplified

Is it possible to search for dates and/or text is the same statement




Private Sub cboFILTERS_AfterUpdate()

On Error GoTo CantFilterForm

FilterOn = False

With cboFILTERS

If .ListIndex >= 0 Then Filter = .Column(1, .ListIndex): GoTo Inlist
'preset filter is fine

'Cant help but think the following could be simplyfied:

'---------------------------------------------------------------------------------

If IsDate(.Value) Then
Dim f As Long

f = CDate(.Value)

Filter = "[Enquired] = " & f _
& " or int([Booked]) = " & f _
& " or [invite sent] = " & f _
& " or [start date] = " & f _
& " or [DOB] = " & f _
& " or [Exit date] = " & f _
& " or [Job Outcome] = " & f
Else
Filter = "[first name] Like '*" & .Value & "*' or " _
& "[Last name] Like '*" & .Value & "*' or " _
& "[Address] Like '*" & .Value & "*' or " _
& "[town] Like '*" & .Value & "*' or " _
& "[A49] Like '*" & .Value & "*' or " _
& "[Interest] Like '*" & .Value & "*' or " _
& "[Ni NO] Like '*" & .Value & "*'"
End If

'------------------------------------------------------------------

End With
Inlist:
FilterOn = True
With RecordsetClone
.MoveLast
.MoveFirst
lblCount.Caption = "Record " & Me.CurrentRecord & " of " &
..RecordCount
If .RecordCount = 0 Then MsgBox "No Matches Found": FilterOn = False
End With
Exit_FilterForm:
Exit Sub
CantFilterForm:
MsgBox "Cant Filter Form: " & Err.Description
Resume Exit_FilterForm
End Sub

 

[Chris K]

I'm using one combo (cboFilters) so the user can filter my form by either
choosing preset/sql filters from dropdown or just typing in date or text

It's all working fine except inconstant error - dont expect anyone to
error
check this routine but I'm wondering if it can be simplified

Is it possible to search for dates and/or text is the same statement

Private Sub cboFILTERS_AfterUpdate()

On Error GoTo CantFilterForm

FilterOn = False

With cboFILTERS

If .ListIndex >= 0 Then Filter = .Column(1, .ListIndex): GoTo
Inlist
'preset filter is fine

'Cant help but think the following could be simplyfied:

'---------------------------------------------------------------------------------

If IsDate(.Value) Then
Dim f As Long

f = CDate(.Value)

Filter = "[Enquired] = " & f _
& " or int([Booked]) = " & f _
& " or [invite sent] = " & f _
& " or [start date] = " & f _
& " or [DOB] = " & f _
& " or [Exit date] = " & f _
& " or [Job Outcome] = " & f
Else
Filter = "[first name] Like '*" & .Value & "*' or " _
& "[Last name] Like '*" & .Value & "*' or " _
& "[Address] Like '*" & .Value & "*' or " _
& "[town] Like '*" & .Value & "*' or " _
& "[A49] Like '*" & .Value & "*' or " _
& "[Interest] Like '*" & .Value & "*' or " _
& "[Ni NO] Like '*" & .Value & "*'"
End If

'------------------------------------------------------------------

End With
Inlist:
FilterOn = True

[]

Literal date values must be enclosed in # signs. Because of
the possibility of different Windows regional settings for
date styles, I prefer to use:
"[Enquired] = " & Format(f, "\#yyyy-m-d\#")

I stumbled upon it myself but you were spot on the money
I had to wrap the date in # but also had to change it to US using Format
(mydate, "mm/dd/yy") but it works fine now - thanks

You must think it strange but it was mis-interpreting dates entered as a
string "14/7/10" (14th July UK) as 7th day of 14th month and erroring - only
noticed as a fluke when i typed 7/6/10 and it found 6th July dates (not 7th
June as anticipated)

It's not clear what you are doing on the line:
& " or int([Booked]) = " & f _
but if you are tring to ignore a time part, I suggest that
you use DateValue instead of Int

So, because of the different syntax to search for number,
text and date fields, the kind of code you have is a good
way.