Query Parameter

[ram]

Is there a way to use a query parameter with an expression. Or reference a
textbox value within an expression. In the expression below I would like to
replace the 9/1/2010 date with a reference to a textbox or parameter message
box:


MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire
Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire
Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))

Thanks for any help

 

[John Spencer]

This should work in most queries.

MeasurementDays:
IIf(DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))<365,
DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))
-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))

Instead of [What Date?] you can use a reference to a control on a form.
Forms![NameOfForm]![NameOfControl]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[ram]

HI John,

I received the following error message: The MS Jet Database engin does not
recognize the form and control as a valid field name.

Do you know what I'm doing wrong?

Thanks again for any help

This should work in most queries.

MeasurementDays:
IIf(DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))<365,
DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))
-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))

Instead of [What Date?] you can use a reference to a control on a form.
Forms![NameOfForm]![NameOfControl]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Is there a way to use a query parameter with an expression. Or reference a
textbox value within an expression. In the expression below I would like to
replace the 9/1/2010 date with a reference to a textbox or parameter message
box:


MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire
Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire
Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))

Thanks for any help

.

 

[John Spencer]

Is the form open? It must be open to be available to the SQL (although the
form can have its visible property set to No).



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

HI John,

I received the following error message: The MS Jet Database engin does not
recognize the form and control as a valid field name.

Do you know what I'm doing wrong?

Thanks again for any help

This should work in most queries.

MeasurementDays:
IIf(DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))<365,
DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))
-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))

Instead of [What Date?] you can use a reference to a control on a form.
Forms![NameOfForm]![NameOfControl]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Is there a way to use a query parameter with an expression. Or reference a
textbox value within an expression. In the expression below I would like to
replace the 9/1/2010 date with a reference to a textbox or parameter message
box:


MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire
Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire
Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))

Thanks for any help

.

 

[David Jennings]

Send US$. RSVP