Filter records by date string

[Thomasso]

Hi,



I have a continuous form with filter text boxes.

I want to filter records based on what I type in these textboxes. I am struggling with filtering dates at the moment.

Date is shown as 31.12.2024 (EU format with a dot).

I want this functionality:

• I type .12. in the filter box: All december records will show, regardless of year
• I type 12 (without dots): All december and 12th day records
• I type .2025 - all records from year 2025
• I type .07.2023 - all records from July 2023

I can't make it work with the dot. Here is what I have:

Me.RecordSource = "SELECT * " & _
                        "FROM v_PurchaseOrders " & _
                        "WHERE CSTR(PurchaseOrderDate) LIKE '*" & Nz(Me.txtFilterPurchaseOrderDate, "") & "*' " & _
                        "ORDER BY PurchaseOrderDate DESC , PurchaseOrderID DESC"

It works when I only type numbers, but starts acting really funny when adding the dots. Any ideas what might be the issue?
Thanks.
Tomas

[Welshgasman]

Create your own function for your unique requirements and use the result of that for your filter.

[jojowhite]

You can try:

Me.RecordSource = "SELECT * " & _
                        "FROM v_PurchaseOrders " & _
                        "WHERE PurchaseOrderDate = #" & Format$(Me.txtFilterPurchaseOrderDate, "mm/dd/yyyy") & "# " & _
                        "ORDER BY PurchaseOrderDate DESC , PurchaseOrderID DESC"

[jojowhite]

also why not use a 2 combobox (one with 1-12, for the months and the other combo with the years on it).
so that your filtering will be much easier.

[jojowhite]

demo for filtering using 2 comboboxes

[Thomasso]

demo for filtering using 2 comboboxes

Cool stuff, however I would like to stay with my approach, as typing "12.2025" is faster for me than selecting 2 combo boxes.

Unfortunately I still cannot make this work. The suggestion with Format and # did not work either, I suspect it has something to do with my Czech locale settings.

[Welshgasman]

The suggestion with Format and # did not work either, I suspect it has something to do with my Czech locale settings.

I would have thought it would be because you are not supplying a full date? That format expects a full date.

Try a few debug.prints to see what you have.

Your complicated logic means that you need a narrow tailored approach to get the results you want. I believe only a dedicated function can do that.

[jojowhite]

here test Orders2 form and see the code on the Filter/Unfilter button.
enter those functionality you mentioned on post #1.

[Thomasso]

That is what's confusing to me the most... I already tried Debug.Print, before I even created this post. Results looked like it should work, correctly showing the dots and everything...

[Thomasso]

here test Orders2 form and see the code on the Filter/Unfilter button.
enter those functionality you mentioned on post #1.

I'll take a look at this tomorrow, the code looks promising, thank you!

[Welshgasman]

That is what's confusing to me the most... I already tried Debug.Print, before I even created this post. Results looked like it should work, correctly showing the dots and everything...

Well it would for the variable, but what are you comparing it to?
01.12.2025 is not the same as 2025 or .12
I cannot see Like being of any use either?, even if it could be used for dates?

[Thomasso]

Well it would for the variable, but what are you comparing it to?
01.12.2025 is not the same as 2025 or .12
I cannot see Like being of any use either?, even if it could be used for dates?

Used 2 Debug.Print statements, one for the date table field (CSTR - converted to string) and one for the textbox. Both were showing the same, including the dot.

LIKE is surely useful in this scenario, if you use an asterisk on both ends. LIKE "*.12*" should surely match to 01.12.2025

[Welshgasman]

Used 2 Debug.Print statements, one for the date table field (CSTR - converted to string) and one for the textbox. Both were showing the same, including the dot.

LIKE is surely useful in this scenario, if you use an asterisk on both ends. LIKE "*.12*" should surely match to 01.12.2025

Well it seems it does

SELECT tblDaily.*, tblDaily.DailyDate
FROM tblDaily
WHERE (((tblDaily.DailyDate) Like "*/12*"));

works for my date format.
I have never used Like for dates.

[Micron]

See if this helps http://allenbrowne.com/ser-36.html
You may get a browser warning re: the site not using https. Many of us have been visiting or referring to that site for a long time know, so I don't worry about it.

I believe you can use Like because you've cast your dates as strings and try to sort on that. Numbers as strings will not sort as you expect - 211 comes before 22. I agree that your only solution is probably going to be vba code.

[moke123]

Just a thought, why not break the date up into Month(your date), Day(your date), Year(your date) in your query
and then use those fields in your filter.

SELECT Table1.ID, Table1.dteDate, Month([dtedate]) AS m, Day([dtedate]) AS d, Year([dtedate]) AS y
FROM Table1;

Dim strfilter As String


strfilter = "M = """ & Me.Text8 & """ Or D = """ & Me.Text8 & """ or Y = """ & Me.Text8 & """"
Debug.Print strfilter
Me.Filter = strfilter
Me.FilterOn = True

M = "30" Or D = "30" or Y = "30"
M = "2024" Or D = "2024" or Y = "2024"
M = "7" Or D = "7" or Y = "7"