Dear All,

I have the following fields (expressions) in my query based on certain fields from my table

Expr4: DateAdd("yyyy",18,[dob])

Expr1: Date()

Expr2: DateAdd("d",30,Date())

Expr3: IIf((([Expr1]<=[Expr4]) And ([Expr4]<=[Expr2])),"Yes","No")

Run just like that I get the desired results i.e. I am getting those records which conform to Yes and those to No.

However when I further filter the query by entering “Yes” in the criteria of the field in my effort to try and select only those records which result in “Yes” I get asked to enter the data for Expr1, then Expr4 and then Expr2.

Grateful for ideas why this is happening.
Who is Participating?
IrogSintaConnect With a Mentor Commented:
Unfortunately, you cannot introduce criteria to expressions that are referred to in other expressions.  As gustav said you would need to replace the expressions in Expr3 with the actual formulas themselves.  Your final expression would end up like this:
IIf(((Date()<=DateAdd("yyyy",18,[dob])) And (DateAdd("yyyy",18,[dob])<=DateAdd("d",30,Date()))),"Yes","No")

If you're trying to see if their DOB is within the next 30 days, you can change Expr3 this way:
IIf(DateDiff("d",Date(),DateAdd("yyyy",18,[DOB])) Between 0 And 30,"Yes","No")
Gustav BrockConnect With a Mentor CIOCommented:
You have to replace in Expr3 the other expression namess (Expr1, 2, 4) with their literal expressions.

PipMicAuthor Commented:

What write the entire expression inside Expr3??
PipMicAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.