avoiding parameter queries

L

LAF

How does one avoid parameter queries? For example, I have a query with two
date fields, and a calculated field [FinalDate]that uses an IIf to determine
which of the two date fields is more recent and to set FinalDate to that
value. The query works fine. Then when I specify a date criterion for
FinalDate, msaccess always pops up a parameter box that asks for the value of
FinalDate. The FinalDate field obviously has a value, and the criterion
specifys a year with Year([FinalDate]) = 1999, so what needs to be done to
prevent the parameter box from opening?

Thanks,
LAF
 
R

Rick Brandt

LAF said:
How does one avoid parameter queries? For example, I have a query
with two date fields, and a calculated field [FinalDate]that uses an
IIf to determine which of the two date fields is more recent and to
set FinalDate to that value. The query works fine. Then when I
specify a date criterion for FinalDate, msaccess always pops up a
parameter box that asks for the value of FinalDate. The FinalDate
field obviously has a value, and the criterion specifys a year with
Year([FinalDate]) = 1999, so what needs to be done to prevent the
parameter box from opening?

Thanks,
LAF
Click to expand...

[FinalDate] is not a real field. It is an alias and you cannot refer to an
alias in the same query. You have to repeat the expression that is used by
the alias.

Instead of...

Year([FinalDate]) = 1999

....you have to use...

Year(Whatever If() expression is used in [FinalDate]) = 1999
 
L

LAF

Thanks, Rick. I was unable to find this in access help, or even in books on
access.

LAF

Rick Brandt said:
LAF said:
How does one avoid parameter queries? For example, I have a query
with two date fields, and a calculated field [FinalDate]that uses an
IIf to determine which of the two date fields is more recent and to
set FinalDate to that value. The query works fine. Then when I
specify a date criterion for FinalDate, msaccess always pops up a
parameter box that asks for the value of FinalDate. The FinalDate
field obviously has a value, and the criterion specifys a year with
Year([FinalDate]) = 1999, so what needs to be done to prevent the
parameter box from opening?

Thanks,
LAF
Click to expand...

[FinalDate] is not a real field. It is an alias and you cannot refer to an
alias in the same query. You have to repeat the expression that is used by
the alias.

Instead of...

Year([FinalDate]) = 1999

....you have to use...

Year(Whatever If() expression is used in [FinalDate]) = 1999
Click to expand...
 
Top