Textbox to search and filter date field in access (1 Viewer)

Bone2

New member
Local time
Today, 17:37
Joined
Apr 22, 2020
Messages
21
I am trying to update my access database. I have addedd a textbox called txtSearch on the form. The intention is to search and filter records in the a table called Inspections_LogBookTable as the user types in the textbox called txtSearch. The code works perfectly for text fields but it cannot filter date fields.
the code I'm using is below
Code:
Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo Err1
Dim FilterValue As String
'Apply or update filter based on user input.
If Len(Me.txtSearch.Text) > 0 Then
   FilterValue = Me.txtSearch.Text
   Me.Form.Filter = "[Inspections_LogBookTable]![Invoice_Number] LIKE '*" & FilterValue & "*' or [Inspections_LogBookTable]![Amount] LIKE '*" & FilterValue & "*' or [Inspections_LogBookTable]![Purpose] LIKE '*" & FilterValue & "*' ' or [Inspections_LogBookTable]![Inspection_Date] LIKE '*" & FilterValue & "*'""
   Me.FilterOn = True
  'Retain filter text in search box after refresh.
   Me.txtSearch.Text = FilterValue
   Me.txtSearch.SelStart = Len(Me.txtSearch.Text)
Else
   ' Remove filter.
   Me.Filter = ""
   Me.FilterOn = False
   Me.txtSearch.SetFocus
End If

Exit Sub

Err1:

    MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub


Some of the columns in the table are ; Invoice_Number, Amount, Purpose (text data type) and Inspection_Date (Date datatype).

Any help on how to filter the fields with date data type
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:37
Joined
May 21, 2018
Messages
8,463
Like only works on strings. It will not work on a date. If the form dates are not to be edited then in the query for the form you can convert those dates to string
Select cstr([SomeDateField]) as SomeNewName,.....

If you want to be editable then you cannot combine the search. You would need a second textbox to search for dates.
In that case your search needs to format the date to mm/dd/yyyy and delimit it.

"#" & format(txtBoxDate,"mm/dd/yyyy") & "#"

may want to see CSQL function in this thread

Another option is to keep the date field and add a converted date field to string. Then you can keep the single textbox but filter on the converted date field.
 

Bone2

New member
Local time
Today, 17:37
Joined
Apr 22, 2020
Messages
21
Thanks @MajP for the quick reply. I will implement what you have suggeted and revert.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:37
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

I would also suggest using the Change event rather than KeyUp. You don't really need to perform the search if the user press the Shift key or the Ctrl or the Alt keys. Just a thought...
 

Bone2

New member
Local time
Today, 17:37
Joined
Apr 22, 2020
Messages
21
Like only works on strings. It will not work on a date. If the form dates are not to be edited then in the query for the form you can convert those dates to string
Select cstr([SomeDateField]) as SomeNewName,.....

If you want to be editable then you cannot combine the search. You would need a second textbox to search for dates.
In that case your search needs to format the date to mm/dd/yyyy and delimit it.

"#" & format(txtBoxDate,"mm/dd/yyyy") & "#"

may want to see CSQL function in this thread

Another option is to keep the date field and add a converted date field to string. Then you can keep the single textbox but filter on the converted date field.
I will be grateful if you can help with how to convert the date field to string as suggested in your last suggestion
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:37
Joined
May 21, 2018
Messages
8,463
In your query. In a blank field. Type a new name and then : followed by the cstr([fieldName])
StrInspectionDate: cstr([Inspection_Date])
 

Bone2

New member
Local time
Today, 17:37
Joined
Apr 22, 2020
Messages
21
In your query. In a blank field. Type a new name and then : followed by the cstr([fieldName])
StrInspectionDate: cstr([Inspection_Date])
I have implemented your suggestion, it works fine. However, the format for the date is now dd/mm/yyyy. How do I change the format to yyyy-mm-dd since that is the format my company uses. Thank you
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:37
Joined
May 21, 2018
Messages
8,463
Since you are converting it to string you can use the format function instead of the CSTR function
NewFieldName: Format([DateField],"dd/mm/yyyy")
 

Users who are viewing this thread

Top Bottom