Solved Calculated field based on 2 conditions. (1 Viewer)

Lochwood

Registered User.
Local time
Yesterday, 17:49
Joined
Jun 7, 2017
Messages
113
Need help with this one guys. I have a calculated field called Classification: when date is null it calculates the word "UNCLASSIFIED" pretty straight forward but i cant quite get the syntax for 2 conditions.. Like if [Registered_date] is not null and [terminated_date] is null then calculate word CLASSIFIED.. Anything i try conflicts with the Unclassified expression

Heres my expression so far that works for Unclassified

Classification: (IIf(IsNull([Registered_date]),"UNCLASSIFIED") & IIf(Not IsNull([Terminated_date]),"UNCLASSIFIED"))
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:49
Joined
Sep 21, 2011
Messages
7,511
Seems to me the RegisteredDate deems whether it is Classified or Unclassfied?
What happens when the TerminatedDate is not Null?
 

Lochwood

Registered User.
Local time
Yesterday, 17:49
Joined
Jun 7, 2017
Messages
113
Seems to me the RegisteredDate deems whether it is Classified or Unclassfied?
What happens when the TerminatedDate is not Null?
Yes this would be the case until terminated date is populated. then it would be Unclassified..

Registered_Date and no Terminated_Date = Classified
Registered_Date and Terminated_Date = Unclassified
Why i need 2 conditions for Classified or i endup with UNCLASSIFIEDCLASSIFIED
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:49
Joined
Sep 21, 2011
Messages
7,511
Try
Code:
IIF(Registered_Date is NOT Null,IIF(Terminated IS Null,"Classified","Unclassified"),"Unclasified")
[code]
 

Lochwood

Registered User.
Local time
Yesterday, 17:49
Joined
Jun 7, 2017
Messages
113
IIF(Registered_Date is NOT Null,IIF(Terminated IS Null,"Classified","Unclassified"),"Unclasified")
Genius!!! so looking a the string we have first condition, second condition, first result, second result, else Would that be right?
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:49
Joined
Sep 21, 2011
Messages
7,511
If the registereddate is set then we look at the terminateddate, if not set then we know it must be Unclassified.
Same with terminateddate, just that the logic is reversed.

if you have more fields to check, I would probably write a dedicated function.
 

Users who are viewing this thread

Top Bottom