vbadatedatetimems-accesscontinuous-forms

Continuous form date filter

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 & "#"
Solution

  • 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") & "#"