Continuous form date filter

0
votes

I'm trying to filter a continuous form by a range of dates using start date and end date text boxes. My code just filters everything out even though I have records that should fit the criteria. A potential issue I've noticed (but am unaware of how to resolve) is that my text boxes are formatted "MM/DD/YYYY" and my table field is a datetime format.

Dim datFrom As Date, datTo As Date

datFrom = txtFrom.Value
datTo = txtTo.Value
Debug.Print datFrom
Debug.Print datTo
DoCmd.SetFilter wherecondition:="expFrom BETWEEN #" & datFrom & "# AND #" &   datTo & "#"
1
vbadatedatetimems-accesscontinuous-forms
Does Debug.Print Format(datFrom, "mmm d yyyy") show you the date you expect? And if you do the same for datTo? - HansUp
Good. Now try formatting those date values like this when you include them in the wherecondition string: "expFrom BETWEEN #" & Format(datFrom, "yyyy-m-d") & "# AND #" & Format(datTo, "yyyy-m-d") & "#" - HansUp
It's the same result as before. - Cloude
Is your continuous form a subform to a main form containing the textboxes? - LiamH
No, the textboxes are in the header of the form. - Cloude

1 Answers

1
votes

I figured it out. Even though the field was originally a date field Access was switching it to a text field so I had to convert it back to a date.

DoCmd.SetFilter wherecondition:="CDate(expFrom) BETWEEN #" & Format(datFrom, "yyyy-m-d") & _
"# AND #" & Format(datTo, "yyyy-m-d") & "#"