creating a time range filter when the data type of dates is text-CodePudding

I have a table with order date, usually most of the data is date, but sometimes I need to write text, so that's why I want to choose text as data type. My data in the table is as follows:

BestellDatum

20.10.2021

22.10.2021

03.01.2022

02.01.2022

23.01.2022

I have a query I created from this table and a form I created from this query. In this form I want to filter by date range (two text fields, names txt.orders_from and txt.orders_to). My code for this:

Private Sub SearchbyDateRange_Click()

Dim strCriteria, task As String

Me.Refresh

If IsNull(Me.txt.orders_from) Or IsNull(Me.txt.orders_to) Then

MsgBox "Please enter a date range"

Me.txt.orders_from.SetFocus

Else

strCriteria = "([OrderDate] >= #" & Format(Me.txt.orders_from, "yyyy/mm/dd") & "# And [OrderDate] <= #" & Format(Me.txt.orders_to, "yyyy/mm/dd") & "#)"

task = "select * from qryOrders where(" & strCriteria & ") order by [OrderDate]"

DoCmd.ApplyFilter task

End If

End Sub

However, if I try to search for the period from 20.10.2021 to 03.01.2022, for example, no dates are displayed. This code works when the OrderDate column has the data type as date/time. Is there anything I can do to use this code with data type as Text or do you have any other idea?

CodePudding user response：

Format expects a Date as the first parameter and you are feeding it a Text. Instead of trying to convert text into different text what you need is a way to convert your text into a date.

The function you are looking for to convert text to a date value and make date comparisons is CDate.

I am not 100% sure CDate will be able to read your specific date format but it is worth a try. If not you will have to do the hard work of breaking it down into a text array and recombining it to a more compatible format.

CodePudding user response：

Convert your text dates to true dates:

strCriteria = "CDate(Replace([OrderDate], ".", "-")) >= #" & Format(Me.txt.orders_from, "yyyy\/mm\/dd") & "# And CDate(Replace([OrderDate], ".", "-")) <= #" & Format(Me.txt.orders_to, "yyyy\/mm\/dd") & "#"