Choice of Criteria

[Peter]

Hi all!

I use the below in my Selct Query, but i can not figure out how to set the
criteria days..i.o..i want to see all "cases" with an "age" (days) example 20
days...i am pulling my hair..but nothing..keeps asking me for "Today"

Days: IIf([Case Status]="Open" Or [Case Status]="Review",[Today]-[Date
Created])

Thanks again for all help!

 

[Peter]

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:

WHERE [Case Status] = "Open"
OR ([Case Status] = "Review" AND DateDiff("d", [Date Created], Date()) >= 20)

HTH
Dale

Hi all!

I use the below in my Selct Query, but i can not figure out how to set the
criteria days..i.o..i want to see all "cases" with an "age" (days) example 20
days...i am pulling my hair..but nothing..keeps asking me for "Today"

Days: IIf([Case Status]="Open" Or [Case Status]="Review",[Today]-[Date
Created])

Thanks again for all help!

 

[Peter]

Thank you very much Dale, indeed this is a very helpfull, i will try out
both options, once again, Thanks!

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!