Using Multiple Calculated Date Fields in a Query - Looking for the Best Way

[SueHopson]

Currently, my query has 2 calculated query fields, where one is based upon the other (and I remember vaguely that this doesn't work, but can't remember the solution)

WHMIS Next Renew: IIf([WHMIS Completed] Is Null,"",IIf([WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed])))

WHMIS Status: IIf([WHMIS Next Renew]="","Required", IIf([WHMIS Next Renew]<Date(),"Expired","Current"))

WHMIS Next renew does exactly what I need it to do, but the WHMIS Status does not... it returns the Required Values Fine but the Expired and Current values incorrectly.

Can someone remind me why this doesn't work and how to fix - or provide a better way of doing this?
Thanks!

[isladogs]

If you want to use the same query, you need to replace both instances of WHMIS Next Renew in the second expression with the full expression: IIf([WHMIS Completed] Is Null,"",IIf([WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed])))

However, that's going to get very messy with two sets of IIf statements.
So I would suggest using two queries with WHMIS Next Renew done in query 1 then use that as a field to derive WHMIS Status in query 2

[SueHopson]

Perfect! I knew it was something simple, I just could not for the life of me remember.
Thank you.

[isladogs]

You're very welcome.

[NeoPa]

Hi Sue.

Generally speaking calculated fields within a query work fine. However, this is not true when the resultant calculated field is used either in the filtering or sorting (WHERE, ORDER BY or GROUP BY clauses). This is because these clauses are processed first and the rest of the fields are then only calculated as and when they're needed. That is necessarily after the first sets.

Typically, as Jet/ACE has no knowledge of the dependent calculated field at the time it processes the depending field, it will prompt the user for it.

Can we assume that [WHMIN Status] is included in one of the other clauses in your original query?

[cactusdata]

Also, your expression can be reduced to:

IIf([WHMIS Completed] Is Null Or [WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed]))

and consider returning Null for an unknown date:

IIf([WHMIS Completed] Is Null Or [WHMIS Term]=0,Null,DateAdd("yyyy",[WHMIS Term],[WHMIS Completed]))

[SueHopson]

To NeoPa and Cactusdata
My thanks for the technical explanation and for the code simplification. This is why I love this forum, clear concise, and effective replies!

[SueHopson]

I took Isladogs advice, once I remembered why, and reorganized my fields more efficiently within 2 queries. My data flows much more smoothly, and yes, [WHMIS Status] was and continues to be a defined Yes/No field.

[NeoPa]

Very pleased to hear Sue. In view of that I've set that post as Best Answer for you :-)