If statement with calculated date field

[UT227]

I have an if statement that isn't working. I calculated a date from another query. It is written like this:


ExpirDate: [DateofTraining]+[DaysToExpir]

I had to use that field [Expir in another query to see if there was an expiration date or not:
ExprDate: IIf(([DateofTraining]=[ExpirDate]),"No Expiration",[ExpirDate])

When I tried to use that field [ExprDate] in another query, it didn't work right:
Expired: IIf(([ExprDate]<Date()),"EXPIRED","")

I got the opposite effect I was trying for:


I know that I nested queries, but figured this would work. Everything else seemed to work right. Any ideas?

[Micron]

If [ExprDate] is the result of the expression IIf(([DateofTraining]=[ExpirDate]),"No Expiration",[ExpirDate]), then the result must be a variant because it is either "No Expiration" or it appears to be a date: [ExpirDate]. Certainly, if the result is No Expiration, it cannot be less than <Date(). That would be an invalid comparison, thus it defaults to the part of the IIF statement that is to be chosen if the first part is False, which is EXPIRED

[UT227]

Then wouldn't all of them be EXPIRED?

[UT227]

Possible solution?

[UT227]

OK. Figured out a solution. Used a query that didn't have that particular if statement. Thanks.

[Micron]

Then wouldn't all of them be EXPIRED?

what if this IIf(([DateofTraining]=[ExpirDate]),"No Expiration",[ExpirDate] resulted in a valid date? Wouldn't subsequent comparisons to Date() be valid?
It's my understanding that this ExpirDate: [DateofTraining]+[DaysToExpir] is a valid date calculation. Perhaps I am not correctly deciphering the outcomes from all of your expressions.

[UT227]

That is the if statement that I used to get "No Expiration". It works great.
ExprDate: IIf(([DateofTraining]=[ExpirDate]),"No Expiration",[ExpirDate])
However, when I try to add an if statement to show that the ExprDate is < Date(), that's when it stops working.
Expired: IIf(([ExprDate]<Date()),"EXPIRED","")
I get it showing that the dates that are not expired showing as expired.

[Micron]

I think you are missing the point. Let's assume this results in a valid date
ExpirDate: [DateofTraining]+[DaysToExpir], so 03/26/2018 as an example

This ExprDate:IIf(([DateofTraining]=[ExpirDate]),"No Expiration",[ExpirDate]) should produce 1 of 2 outcomes
ExprDate is either "No Expiration" OR a date, and I've already determined that the date is 03/26/2018

Then this Expired:IIf(([ExprDate]<Date()),"EXPIRED","") is either saying
Expired:IIf((03/26/2018<Date()),"EXPIRED","") which should be fine as we can compare any date to Date()
but if ExprDate is "No Expiration" you cannot compare that to any date, thus your query will produce incorrect results. How can the text No Expiration be compared to any date? It matters not that your prior expression evaluates as you expect if subsequent uses of it's values don't.

If I'm the one who is missing something here then I apologize and appreciate your patience.

[UT227]

I might be missing the point. I understand why it doesn't work. I was wondering if that had affected the outcome of the query. I agree that it did. I changed it so that it now doesn't use that if statement. I have it working so that I'm showing the correct dates being shown as expired. Now, I have another problem that popped up. Because those dates that have no expiration are equal to the [DateofTraining], they show up as being expired because they fit the statement as being <Date(). Now, I'm trying to find a way to filter those out.

[Micron]

Seems to me that if we are to continue you should post a sample of data along with what the outcome should be. A zipped copy of your db would probably be even better.