Solved Filter by date a variable number of days into the future.

[lwvogan]

I am trying to filter a form that displays data from an employees table. Each employee record has a scheduled departure date: [EndDate]. I want a user to be able enter the number of days into the future that they want to see upcoming departures: textBoxDays. For example, if the user wants to see a list of employees that will depart in the next month, they would enter 30. If they want to see the departure list for the next six months, they enter 180. In a query that works for a set period, the criteria for the field EndDate is < Date()+30. I want to be able to have EndDate < Date() + textBoxDays.

I have tried many combinations in the after update event but none seem to work. Thanks in advance.

 

[June7]

I would build filter criteria in VBA:

Me.Filter = "EndDate BETWEEN Date() AND Date() + " & Me.textBoxDays
Me.FilterOn = True

If you must have parameterized query object, then need full reference to form and control, something like:

SELECT Employees.* FROM Employees WHERE EndDate BETWEEN Date() AND Date() + Forms!formname.textBoxDays

 

[arnelgp]

you already have the correct criteria for the query:
Between Date() And DateAdd("d",Date(),[Forms]![FormEmployees]![txtDays])

 

[lwvogan]

I tried “Me.Filter = "EndDate BETWEEN Date() AND Date() + " & Me.textBoxDays
Me.FilterOn = True” in the After Update event for the text box (textBoxDays) and it worked perfectly. I will check out the other options when I get on my computer. Thank you both.