Trying to filter date with textbox input (1 Viewer)

[5hadow]

Can someone explain why this doesn't work?

Private Sub btnFilterDate_Click()
    Dim DueDate As Date
    DueDate = DateAdd("d", Me.tbxFilter.Value, Now())
    Me.Filter = "ReviewDue <= duedate"
    Me.FilterOn = True
End Sub

I want to filter ReviewDue by today + what ever number is in tbxfilter.

 

[theDBguy]

Try:

Me.Filter = "ReviewDue<=" & DueDate

 

[pbaldy]

Try

Me.Filter = "ReviewDue <= #" & duedate & "#"

 

[arnelgp]

in case your Regional date setting is not EN(US):

Me.Filter = "ReviewDue <= #" & Format$(duedate, "mm\/dd\/yyyy") & "#"

 

[Pat Hartman]

As long as the control is bound to a date data type field or if the field is unbound, then the format must specify a date format, then this will work:

Me.Filter = "ReviewDue<=" & Me.DueDate

It doesn't need octothorps because it isn't formatted.
It doesn't need to be formatted to the US date format because it is NOT a string

ONLY strings require octothorps and formatting.

Date data types are double precision NUMBERS. The integer portion of the value is the number of days since Dec 30, 1899 (other RDBMS' use different origin dates. I think SQL Server uses Jan 1,1900). The decimal portion is the fraction of the day since midnight so

2.25 = Jan 1, 1900 06:00 AM

-1.75 = Dec 29, 1899 06:00 PM

44460.752662037 = Sept 21, 2021 06:04 PM

Never format a date except for display or to make a string for a SQL statement. Formatting a date turns it into a string and so:
01/02/2021 will be less than 01/03/2020 because strings are evaluated character by character left to right and 3 is > 2
That is also why you see "numbers" sort as 1, 10, 11, 12, 2, 20, 21, 20000000, 3, .... when they are stored as text.

 

[pbaldy]

As long as the control is bound to a date data type field or if the field is unbound, then the format must specify a date format, then this will work:

Me.Filter = "ReviewDue<=" & Me.DueDate

It doesn't need octothorps because it isn't formatted.

A brief test would suggest otherwise. The textbox is bound to a field with the date/time data type and the format of the textbox is short date (also doesn't work if the textbox is unformatted). The first line does not error but does not return any records. The second line returns the expected records.

'DoCmd.OpenReport "rptReservations", acViewPreview, , "StartDate = " & Me.StartDate
DoCmd.OpenReport "rptReservations", acViewPreview, , "StartDate = #" & Me.StartDate & "#"

 

[Pat Hartman]

Isn't that interesting. I don't often use the where argument sorry about the bad advice. I usually use the argument inside a querydef. So:

SELECT tblBloodPressure.PressureReadingID, tblBloodPressure.PersonID, tblBloodPressure.ReadingDate, tblBloodPressure.ReadingTime, tblBloodPressure.Systolic, tblBloodPressure.Diastolic, tblBloodPressure.PulseRate, tblBloodPressure.OxygenLevel, tblBloodPressure.Tempature, tblBloodPressure.Mach, tblBloodPressure.ChangeBy, tblBloodPressure.ChangeDT
FROM tblBloodPressure
WHERE (((tblBloodPressure.ReadingDate)<[forms]![frmPeople]![txtDate]));


Works just fine.

I'm guessing the difference is exactly what i said earlier. When you are building a string, which the WHERE argument of the OpenReport Method is as well as the value for a form filter, you need the octothorpes. You would also need the formatting if your standard date is NOT mdy.

I hate inconsistencies like this but I've discovered a few over the years. Some of them MS has even documented itself like the way Nz() and the IIf() work in VBA vs in a query. Here's another one for my list. So far, the differences are different rules for querydefs vs VBA. What surprised me this time is that the WHERE worked fine even though I deliberately didn't add a format to the unbound control. Not so long ago, Access wasn't smart enough to work that out so that trap has been fixed at least.

 

[5hadow]

Me.Filter = "ReviewDue <= #" & duedate & "#"

Hey everyone.

Just like to report that it works using this line.

 

[pbaldy]

Glad it worked for you.