Filter by date

[John]

I wonder who could help me with this? In the after update event of an Option
Group I've entered the following code:

Private Sub FilterBy_AfterUpdate()
With Me.[SubForm_BookLabels].Form
Select Case FilterBy
Case Is = 1 'this is for filtering by today's date
.Filter = "[date]= " & Date
.FilterOn = True

Case Is = 2 ' this is for filtering by all the dates equal or
after [DateToFilter]
.Filter = "[Date] >= " & Me.[DateToFilter]
.FilterOn = True

Case Is = 3 ' no filter at all
.FilterOn = False

End Select
Me![SubForm_BookLabels].Requery
End With
End Sub

The code doens't give me any error, but it seems that it doesn't filter by
date as it is expected to do.

I wonder what I did wrong,

Thank you

John

 

[Allen Browne]

1. If DateToFilter is an unbound control, set its Format property to Short
Date or similar, so Access knows to interpret the value as a date.

2. Explicitly format the date as required by JET, with the # delimiters:
.Filter = "[date]= " & Format(Date, "\#mm\/dd\/yyyy\#")

3. If you really have a field named Date, Access is likely to get confused.
If possible rename the field to something that is not a reserved word, and
then change all the queries, forms, reports, and code that use this name. If
you are kinda stuck with the name, it may help to tell Access you mean the
Date provided by the VBA library, i.e.:
.Filter = "[date]= " & Format(VBA.Date, "\#mm\/dd\/yyyy\#")