Help With Expressions Within Expressions

[Guest]

I have a query that has a calculated field Called HireDate which has the
following expression
HireDate: IIf(IsNull([ADJ_HIRE_DT]) Or
[LOCATOR_CD]<>"OSPV",[ORIG_HIRE_DT],[ADJ_HIRE_DT])

and also has another calculated field called YOS that references the first
Calculated field (HireDate)

YOS: Int(#12/31/2006#-[HireDate]/365.25)

Now I would like to set criteria gainst the YOS Calculated field to only
display values that are 5,10,15,20,or 25.

the problem is is that whenever I put any criteria against YOS I get a
parameter query pop-up that says HireDate (insert value here). It is like it
reads the HireDate calculated field reference as a parameter. I cannot change
the '[ ]' s they are added automatically by access. can anyone help me out
here or explian why it does not work ?

 

[Marshall Barton]

I have a query that has a calculated field Called HireDate which has the
following expression
HireDate: IIf(IsNull([ADJ_HIRE_DT]) Or
[LOCATOR_CD]<>"OSPV",[ORIG_HIRE_DT],[ADJ_HIRE_DT])

and also has another calculated field called YOS that references the first
Calculated field (HireDate)

YOS: Int(#12/31/2006#-[HireDate]/365.25)

Now I would like to set criteria gainst the YOS Calculated field to only
display values that are 5,10,15,20,or 25.

the problem is is that whenever I put any criteria against YOS I get a
parameter query pop-up that says HireDate (insert value here). It is like it
reads the HireDate calculated field reference as a parameter. I cannot change
the '[ ]' s they are added automatically by access. can anyone help me out
here or explian why it does not work ?

 

[Marshall Barton]

I have a query that has a calculated field Called HireDate which has the
following expression
HireDate: IIf(IsNull([ADJ_HIRE_DT]) Or
[LOCATOR_CD]<>"OSPV",[ORIG_HIRE_DT],[ADJ_HIRE_DT])

and also has another calculated field called YOS that references the first
Calculated field (HireDate)

YOS: Int(#12/31/2006#-[HireDate]/365.25)

Now I would like to set criteria gainst the YOS Calculated field to only
display values that are 5,10,15,20,or 25.

the problem is is that whenever I put any criteria against YOS I get a
parameter query pop-up that says HireDate (insert value here). It is like it
reads the HireDate calculated field reference as a parameter. I cannot change
the '[ ]' s they are added automatically by access. can anyone help me out
here or explian why it does not work ?

I don't think I've ever fooled with that kind of criteria in
the query design grid. Switch the query to SQL view and use
this in the WHERE clause:

IIf(IsNull([ADJ_HIRE_DT]) Or [LOCATOR_CD] <> "OSPV",
[ORIG_HIRE_DT],[ADJ_HIRE_DT]) IN(5,10,15,20,25)