Usage of Me.Filter between two dates

[charly.csh]

Hello everybody,



I have in my form (from a query) two unbound fields named "StartDatetxt" and "EndDatetxt" and I want to filter all the records that are between such dates, but this is not working as if. Basically it's filtering all the database showing any record, example 01/12/2021 until 06/12/2021 and it's not showing any record despite I have records between this dates, however something weird happens when I make this time window wider like 01/12/2021 until 31/12/2021 it shows all this records
I hope anyone find something that I would be missing
I highly appreciate support!!


My code is this one


'If Me.StartDatetxt <> "" And Me.EndDatetxt <> "" Then


'Me.Requery
'Me.Filter = "Fecha_Apertura >= #" & StartDatetxt & "# And Fecha_Apertura <= #" & EndDatetxt & "#"
'Me.FilterOn = True


'End If

Thanks!!!

[Bob Fitz]

Perhaps:
'Me.Filter = "[Fecha_Apertura] >= #[StartDatetxt]# And [Fecha_Apertura] <= #[EndDatetxt]#"

Why the Me.Requery ?

[June7]

Or maybe:

Me.Filter = "[Fecha_Apertura] BETWEEN #[StartDatetxt]# AND #[EndDatetxt]#"

[CJ_London]

I presume

01/12/2021 until 06/12/2021

is between 1st and 6th December

However the filter will interpret this as between 12th Jan and 12th June because it uses the US date format. Note that if the date is say 31/12/2021 then it will be interpreted correctly as dd/mm/yyyy because there aren't 31 month in a year. So

when I make this time window wider like 01/12/2021 until 31/12/2021 it shows all this records

means between 12th Jan and 31st Dec. Which suggests you don't have any records for Jan-Nov.

You need to reformat to the US date format of mm/dd/yyyy or the more generic yyyy-mm-dd

"Fecha_Apertura >= #" & format(StartDatetxt,"yyyy-mm-dd) & "# And Fecha_Apertura <= #" & format(EndDatetxt,"yyyy-mm-dd) & "#"

Final note - if Fecha_Apertura contains a time element (which may have been hidden because of formatting the field in the table) then any records for the 'To' day will not be returned since the 'To' date is considered to be midnight - so records for that date with a time element will be after that date.

[ssanfu]

Might also look at International Dates in Access (by Allen Browne).... #2

[charly.csh]

Thanks to everybody in fact all were greats ideas and in this time the issue was as ajax said, my unbound fields are set as US date format and if I write into the fields as Latin date format (dd-mm-yyyy) is taking this instead of December 01, it takes Jan 12th

Thanks for the good ideas!!!!

[charly.csh]

how can I correct this entry from the unbound fields? I mean I made the query as 12/01/2021 till 12/06/2021 (Dec 01th to Dec 06th) and it works as Ajax said, but my users enter data as Latin format (dd/mm/yyyy)

[Welshgasman]

how can I correct this entry from the unbound fields? I mean I made the query as 12/01/2021 till 12/06/2021 (Dec 01th to Dec 06th) and it works as Ajax said, but my users enter data as Latin format (dd/mm/yyyy)

See post #4 ?

[ssanfu]

I use the "Function SQLDate" by Allen Browne (Post #5). I'm in the USA, but when dealing with international dates, I never have to worry when I use "Function SQLDate". Just another option......

[charly.csh]

Good Idea too!