Me.Filter with Dates

Question

Thanks for taking the time to read my question.

I'm stuck on a very simple task and I don't know why.

I have this code:

Function SetRST()
    If Me.fraShowRecs = 1 Then
        Me.Filter = "OrderNum = 12" 'WORKS!!! WHY????
        'Me.Filter = "EarlyDeliveryDate >= " & Date 'DOESN'T WORK
        'Me.Filter = "[EarlyDeliveryDate] >= #03/12/2016#" 'DOESN'T WORK
        'Me.Filter = "[EarlyDeliveryDate] >= 03/12/2016" 'DOESN'T WORK
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    
    Debug.Print Me.EarlyDeliveryDate
    Debug.Print Me.Filter
End Function

When I try to filter my form on a date field it does not filter, but if I filter on a number field it works.

I have 3 records:
1 - EarlyDeliveryDate = 02/12/2016
2 - EarlyDeliveryDate = 02/12/2016
3 - EarlyDeliveryDate = 28/11/2016

I would expect that if 'Me.Filter = "EarlyDeliveryDate >= " & Date then it should filter out the November 28th record, but it is not. I always get 3 records returned when I apply the filter.

I should note today's date is 02/12/2016

Any help is greatly appreciated.

Brad

Answer 1

Brad - VBA needs qualifiers for dates and text.

Me.Filter = "EarlyDeliveryDate >= #" & Date & "#"

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

Hi Bill,

thank you for your reply.

I did try using the #'s in one of my commented out lines.

The result of debug.print Me.Filter is: [EarlyDeliveryDate] >= #03/12/2016#

I would expect to get NO records returned in my form, but still got 3...

Thanks again,

Brad

edit: I just changed my greater than sign to a less than sign and got my form to filter to one record... something is working.... are my expectations wrong?

edit 2:

Me.Filter = "[EarlyDeliveryDate] > #11/12/2016#" DOES NOT WORK

Me.Filter = "[EarlyDeliveryDate] > #12/12/2016#" WORKS!!!!????

edit 3: fixed

Me.Filter = "EarlyDeliveryDate >= #" & Format(Date, "mm/dd/yyyy") & "#" WORKS!!!!

I don't know why, because the rest of my queries all work on dd/mm/yyyy and that is how dates are stored in my tables by default.

Thanks again for your reply Bill. I hope your hospital stay is short :-)

Brad

Answer 3

>Thanks again for your reply Bill. I hope your hospital stay is short :-)

Ha! Not me. It's not often you get PAID to go to the hospital.

I didn't realize you were using dd/mm/yyyy. All you examples looked like mm/dd/yyyy to me. Date() does not change formatting. It is always m/d/yyyy. That's why you had to format it to match your field.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 4

I don't know why, because the rest of my queries all work on dd/mm/yyyy and that is how dates are stored in my tables by default.

The dates are actually stored in the table as a 64 bit floating point number in which the integer part represents the days and the fractional part the time of day.  Zero is 30 December 1899 00:00:00.  By default the values are formatted in the system's regional short date setting, so here in the UK I'd see today as 02/12/2016, while Bill in the USA would see it as 12/02/2016.

 

A date literal must either be in US format or an otherwise internationally unambiguous format.  I always use the ISO standard of YYYY-MM-DD, e.g. "#" & Format([MyDate],"yyyy-mm-dd") & "#".

 

In query design view, however, the local date format is recognized, so you can enter a criterion as >#04/07/1976# for instance, the date being 4^th July, but if you switch to SQL view you'll see that it has become >#7/4/1976#.

---

Ken Sheridan, Stafford, England