You could create a parameter query, but I personally prefer to do this on a
form. I will generally provide an unbound textbox on the form so the user
can enter the number of days old they want to look at, or maybe two boxes if
you want a range of dates.
Then, I use a command button. In this case, assuming you have a continuous
form, and you want to show all the records that are a certain age, you can
probably get away with just setting the forms filter property to a where
clause similar to:
Private Sub cmd_Filter_Click
me.filter = "WHERE [Case Status] = 'Open' OR ([Case Status] = 'Review' AND
DateDiff('d', [Date Created], Date()) >= " & me.txt_DaysOld & ")"
me.filteron = true
endif
If, on the other hand, you want this query to be the source for a report or
something like that, then you might want to build your the SQL for your query
so that it refers to the name of the control on the form. It might look
something like:
SELECT * FROM yourTable
WHERE [Case Status] = "Open"
OR ([Case Status] = "Review" AND DateDiff("d", [Date Created], Date()) >=
Forms!FormName.txt_DaysOld)
HTH
Dale
Hi Dale and Thanks,
this is the whole statement but i want to be able to choose the "days"...i.o
i would like "days" to be a variable...something prompted for input.."- show
all cases that are 20...or 30 days old)...
Thanks
It would be nice to see the rest of the SELECT statement, but I think your
critier clause should look something like:
[quoted text clipped - 15 lines]
Thanks again for all help!