467,877 Members | 1,268 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,877 developers. It's quick & easy.

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

16bit
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

✓ answered by 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

  • viewed: 2709
Share:
8 Replies
isladogs
Expert 128KB
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
16bit
Perfect! I knew it was something simple, I just could not for the life of me remember.
Thank you.
Jan 14 '21 #3
isladogs
Expert 128KB
You're very welcome.
Jan 14 '21 #4
NeoPa
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
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
16bit
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
16bit
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
Expert Mod 16PB
Very pleased to hear Sue. In view of that I've set that post as Best Answer for you :-)
4 Weeks Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by msaccess | last post: by
1 post views Thread by Riley DeWiley | last post: by
2 posts views Thread by Tony K | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.