How to Filter formatted Date fields in MS Access?

[Josh Andrews]

Hi,

I have a combobox named[listDateSelect] which allows users to filter records from my PrepareQuery table/query. The field[listDateSelect] has:

recordsource = "SELECT DISTINCT Format ([PrepareDate],"yyyy-mm") AS [YYYY-MM] FROM PrepareQuery ORDER BY Format([PrepareDate],"yyyy-mm") DESC"

What I need is to format the [PrepareDate] with YYYY-MM

Me.Filter = "Format([PrepareDate],"yyyy-mm") = " & Me.listDateSelect
Me.FilterOn = True
Me.Requery
Me.Refresh

I have tried a number of times in applying the 'Format' function. Though the commands were not prompting any errors, still I cannot get the results I wanted (usually blank or zero records were displayed).


Thanks team,
Josh

[jpatchak]

I would try changing your filter property to:

Me.Filter = "Format([PrepareDate],"yyyy-mm") = """ & Me.listDateSelect & """"

so that you'r comparing string to string (according to Help, the Format function returns a string). If that doesn't work, try:

Me.Filter = "Format([PrepareDate],"yyyy-mm") = #" & Me.listDateSelect & "#"

to compare as a date.

[NeoPa]

Filtering is very much better done with the raw data (in other words date rather than formatted date string). It's important to know and understand exactly what you are comparing with what. Certainly both sides of the comparison must be of the same (preferably) or convertible type (even though any literals - string numeric and date - must be entered into the SQL as a string as that is how SQL commands are issued).

Filtering with dates causes loads of problems, but only because most people don't understand all the issues. Even many experts here treat date comparisons as if the format of the literal date value can be left to the local default, which is not true as it should be formatted as m/d/yyyy whether you are in the USA or in Europe.

Private Const conFilter As String = "([PrepareDate] = #m/d/yyyy#)"

With Me
    .Filter = Replace(conFilter, _
                      "m/d/yyyy", Format(CDate(.listDateSelect), "m/d/yyyy")
    .FilterOn = (.Filter > "")
    Call .Requery
End With

[Josh Andrews]

Hi jpatchak, I tried them both, still there were no records displayed.
NeoPa, actually I really need to filter records with the "yyyy-mm" format. Though I changed your script, no records were displayed :-(

Should I change my approach on filtering data in forms?
Thanks jpatchak and NeoPa.

[NeoPa]

Josh Andrews:
NeoPa, actually I really need to filter records with the "yyyy-mm" format.

Strange comment. Unless of course you really mean you need to filter the dates to match a whole month rather than a single day? That would make sense, and it seems I missed that point from your first post. I'll include below an amended version for you to try.

Josh Andrews:
Though I changed your script, no records were displayed :-(

I'd comment on this, and maybe help you further if I could, but you haven't actually posted anything indicating what you tried, other than that it's different from what I posted. I now realise what I posted would not suit your requirements, but I still can't comment on what you've tried as I have no idea what it was.

Anyway, try this code instead :

Private Const conFilter As String = _
                  "([PrepareDate] Between #m/d/yyyy1# And #m/d/yyyy2#)"
Dim strFilter As String
Dim datThis As Date

With Me
    strFilter = Format(DateAdd("m", 1, CDate(.listDateSelect)), "m/yyyy")
    datThis = CDate(strFilter) - 1
    strFilter = Replace(conFilter, "m/d/yyyy1", datThis, "m/1/yyyy")
    .Filter = Replace(strFilter, "m/d/yyyy2", datThis, "m/d/yyyy")
    .FilterOn = (.Filter > "")
    Call .Requery
End With

As a final explanation that may help your understanding, filtering is most efficient when no special functions (like Format(); Year(); Month(); etc) are used in the SQL itself (as opposed to when creating the SQL string). SQL cannot perform to its best when it has to stop and go elsewhere for each record processed. Hence, though it's easier to code using Format(), it would not be what I recommend.