472,958 Members | 1,468 Online

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

43 32bit
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

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

8 3270
443 Expert Mod 256MB
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
43 32bit
Perfect! I knew it was something simple, I just could not for the life of me remember.
Thank you.
Jan 14 '21 #3
443 Expert Mod 256MB
You're very welcome.
Jan 14 '21 #4
NeoPa
32,547 Expert Mod 16PB
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
210 Expert 128KB
Also, your expression can be reduced to:

Expand|Select|Wrap|Line Numbers
1. IIf([WHMIS Completed] Is Null Or [WHMIS Term]=0,"",DateAdd("yyyy",[WHMIS Term],[WHMIS Completed]))
and consider returning Null for an unknown date:

Expand|Select|Wrap|Line Numbers
1. IIf([WHMIS Completed] Is Null Or [WHMIS Term]=0,Null,DateAdd("yyyy",[WHMIS Term],[WHMIS Completed]))
Jan 15 '21 #6
SueHopson
43 32bit
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!
Jan 26 '21 #7
SueHopson
43 32bit
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.
Jan 26 '21 #8
NeoPa
32,547 Expert Mod 16PB
Very pleased to hear Sue. In view of that I've set that post as Best Answer for you :-)
Jan 27 '21 #9