Criteria using DateDiff function in SELECT Query.

[EricB]

I have a query that used te DateDiff function.
The purpose of the query is to show the records that are
older then 30 days up until 120 days.

I'm using this in the query:
dayselapsed: DateDiff("d",[POdate],Now())
and for the criteria for the field dayselapsed
I have > 30 and <=120.

Is there a way so that my users can specify these numbers
by themselves? So that each time you run the query the
system aks you enter the period and whereby you then
enter the nrs. So for example if you want to see the
records that are older then 1 day up until 60 days you
would enter 1 and 60. Is this technically possible using
a SELECT QUERY??? THANKS FOR ANY SUGGESTIONS...

 

[Jeff Boyce]

Check Access HELP on "parameter query".

Basically, you'll be replacing your criterion statement with something like:

[Enter 'low' days] And <= [Enter 'high' days]

Note that this does NOT give you a mechanism for validating that:
1. a number was entered (unless you also set the parameter type in the
query)
2. a number was entered in both
3, the first number is smaller than the second

To do these things, I'd suggest creating a form you use to gather these, use
the events for the form and the controls to ensure these validation rules
are followed, and then refer to the form's controls in your criterion
statement, something like:

Forms!YourFormName!YourLowestDaysControlName And <=

Forms!YourFormName!YourHighestDaysControlName

(beware of wordwrap in email)

 

[EricB]

This does not work, I have already tried this before.
It is probably due to the fact that I'm using the
DateDiff function in the query.

Thanks anyway....

-----Original Message-----
Check Access HELP on "parameter query".

Basically, you'll be replacing your criterion statement with something like:

[Enter 'low' days] And <= [Enter 'high' days]

Note that this does NOT give you a mechanism for validating that:
1. a number was entered (unless you also set the parameter type in the
query)
2. a number was entered in both
3, the first number is smaller than the second

To do these things, I'd suggest creating a form you use to gather these, use
the events for the form and the controls to ensure these validation rules
are followed, and then refer to the form's controls in your criterion
statement, something like:

Forms!YourFormName!YourLowestDaysControlName And <=

Forms!YourFormName!YourHighestDaysControlName

(beware of wordwrap in email)

--
Good luck

Jeff Boyce
<Access MVP>

.