Parameter Query Using a Calculated Field

[Guest]

I'm trying to build a query for an Issue Tracking database in Access 2003
that will allow users to view all issues that are coming due in the next n
number of days. Each issue has a due date which is stored in a field called
"Agreed Resolution Date." To calculate the number of days until the issue is
due, I've created a calculated field called "DaysPastDue." It is defined as
DaysPastDue: ([Issue Table]![Agreed Upon Resolution Date]-Date()).

To find the issues due in the next 90 days, I would enter the following
criteria in the DaysPastDue field: <=90 And >=0. This returns the
appropriate records. However, I'd like to use a parameter query so users can
pass whatever number of days they'd like to see.

When I change the criteria to <=[parameter] And >=0 and enter a valid number
of days (such as 90), the query does not return the correct records. Any
idea why this does not work correctly? If I change the criteria to just
[parameter] (with no operators) the query works, but including the operators
(<,=) seems to cause problems.

Any help on this would be greatly appreciated. Thanks!

 

[Guest]

Very strange as it works for me. What specifically do you mean by doesn't
return the right records?

Suggestions: Change the criteria to Between 0 And [parameter] . It does
look cleaner, doesn't it?

While in design mode go to Query, Parameters and specifically define the
[parameter] as an Integer. Brackets and exact spelling required.

For testing purposes, change the Date() to Now() in your calculated field.
If things start working right, or close to right, you may have a references
problem. Date often messes up where Now will work. If you stay with Now and
have a time component stored in your [Agreed Upon Resolution Date] field, it
might set things off by one day depending on the time you run the query.

 

[Guest]

Thanks for your help!

It seems to work when I define the parameter [parameter] as an Integer, as
you suggested. It even works with Date().

Regards,
Andrew

Very strange as it works for me. What specifically do you mean by doesn't
return the right records?

Suggestions: Change the criteria to Between 0 And [parameter] . It does
look cleaner, doesn't it?

While in design mode go to Query, Parameters and specifically define the
[parameter] as an Integer. Brackets and exact spelling required.

For testing purposes, change the Date() to Now() in your calculated field.
If things start working right, or close to right, you may have a references
problem. Date often messes up where Now will work. If you stay with Now and
have a time component stored in your [Agreed Upon Resolution Date] field, it
might set things off by one day depending on the time you run the query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to build a query for an Issue Tracking database in Access 2003
that will allow users to view all issues that are coming due in the next n
number of days. Each issue has a due date which is stored in a field called
"Agreed Resolution Date." To calculate the number of days until the issue is
due, I've created a calculated field called "DaysPastDue." It is defined as
DaysPastDue: ([Issue Table]![Agreed Upon Resolution Date]-Date()).

To find the issues due in the next 90 days, I would enter the following
criteria in the DaysPastDue field: <=90 And >=0. This returns the
appropriate records. However, I'd like to use a parameter query so users can
pass whatever number of days they'd like to see.

When I change the criteria to <=[parameter] And >=0 and enter a valid number
of days (such as 90), the query does not return the correct records. Any
idea why this does not work correctly? If I change the criteria to just
[parameter] (with no operators) the query works, but including the operators
(<,=) seems to cause problems.

Any help on this would be greatly appreciated. Thanks!