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!

Jan 14 '21 #1

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

Jan 14 '21 #2

SueHopson

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

Jan 14 '21 #3

isladogs

You're very welcome.

Jan 14 '21 #4

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?

Jan 15 '21 #5

cactusdata

Also, your expression can be reduced to:

and consider returning Null for an unknown date:

Jan 15 '21 #6

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!

4 Weeks Ago #7

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.

4 Weeks Ago #8

NeoPa

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

4 Weeks Ago #9