Search code examples
ms-access

Date Format MS Query vs VBA

I've terrible issue with QUERY date format against VBA Date Format.

On Query I've this:

... DFirst("date_modification","disciplines_date_modification","[what]='qty_released' and [id]=" & [id_dis]) AS date2...

On VBA I need to create a filter:

Me.Filter = "[Date2]=#" & D22 & "#"
Me.FilterOn = True

When I check date e.g.: 26/03/2021 the filter works, but when I check date e.g.: 03/04/2021 the filter will take #04/03/2021# (mm/dd/yyyy).

Thanks for the help.

Solution

  • Your code makes an implicit casting of the date value to a string expression for this.

    You need to force a format of the date value:

    Me.Filter = "[Date2]=#" & Format(D22, "yyyy\/mm\/dd") & "#"

    Side note: DFirst may not take the first/earliest date. DMin will do that.