Query Parameter

  • Thread starter Thread starter ram
  • Start date Start date
R

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
 
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
 
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

John Spencer said:
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
Click to expand...
.
Click to expand...
 
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

John Spencer said:
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
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top