Tom Farrar
asked on
Access 2010 Formula of "IFF" and "AND" Error
Can someone help me with this formula? I am trying to create a nested IIF (two statements) with and "AND". Is that doable, and if so, what am I missing? Thanks.
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2 And IIf(IsNull([Sales / Transfer Qty]),"No","Yes"))
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2 And IIf(IsNull([Sales / Transfer Qty]),"No","Yes"))
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull( [Sales / Transfer Qty]),"No","Yes"))
Remove the AND and pass the second statement in True part
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull( [Sales / Transfer Qty]),"No","Yes"))
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull(
ASKER
Wrong number of arguments error with
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2 And IIf(IsNull([Sales / Transfer Qty])),"No","Yes")
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2 And IIf(IsNull([Sales / Transfer Qty])),"No","Yes")
Remove the AND and pass the second statement in True part
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull( [Sales / Transfer Qty]),"No","Yes"))
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull(
ASKER
This response does not give complete answer.
"Remove the AND and pass the second statement in True part
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull( [Sales / Transfer Qty]),"No","Yes")) "
Some answers are left blank with neither "Yes" or "No".
"Remove the AND and pass the second statement in True part
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull(
Some answers are left blank with neither "Yes" or "No".
ASKER
More specifically, items <= Now()-[Last Received Date])/365)>=2 are blank because they were excluded from the first IIF statement (Apparently).
ASKER
I meant Now()-[Last Received Date])/365)<2 are blank because they were excluded from the first IIF statement (Apparently).
if you want it should return NO if anyone of this statements are True so do it like this
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2 And IsNull([Sales / Transfer Qty]),"No","Yes")
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2 And IsNull([Sales / Transfer Qty]),"No","Yes")
You can use:
Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty]), "Yes/No")
/gustav
Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty]), "Yes/No")
/gustav
ASKER
I am getting "Yes" when Last Received Date])/365)<2 and IsNull([Sales / Transfer Qty]). The first condition is good, but the second condition is not. Answer should be "No".
is Sales / Transfer Qty 2 fields or 1 ?
Now, we don't know your required logic, but with two variables only, the possible combinations are few.
Would it be:
Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And Not IsNull([Sales / Transfer Qty]), "Yes/No")
or:
Expr1: Format(Not (DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty])), "Yes/No")
/gustav
Would it be:
Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And Not IsNull([Sales / Transfer Qty]), "Yes/No")
or:
Expr1: Format(Not (DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty])), "Yes/No")
/gustav
ASKER
Sales / Transfer Qty is one field.
ASKER
The logic is: if two conditions are true:
difference between current date and Last Received Date>= 2 ,
Sales / Transfer Qty is Null
"No", else "Yes".
difference between current date and Last Received Date>= 2 ,
Sales / Transfer Qty is Null
"No", else "Yes".
So why not
IIf(Int((Now()-[Last Received Date])/365)>=2 And IsNull([Sales / Transfer Qty]),"No","Yes")
OR use IIF(DateDiff("d", [Last Received Date], Date())>=2 And IsNull([Sales / Transfer Qty]),"No","Yes")
IIf(Int((Now()-[Last Received Date])/365)>=2 And IsNull([Sales / Transfer Qty]),"No","Yes")
OR use IIF(DateDiff("d", [Last Received Date], Date())>=2 And IsNull([Sales / Transfer Qty]),"No","Yes")
difference between current date and Last Received Date>= 2Int((Now()-[Last Received Date])/365) - will not give you this result
or use Int((Now()-[Last Received Date])) or DateDiff("d", [Last Received Date], Date())
ASKER
Yes, thank you Gozreh, it appears both work when you add /365 to:
DateDiff("d", [Last Received Date], Date())/365
My apology to you, Gustav, I never could get the "Format" formulas to work.
Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty]), "Yes/No")
Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And Not IsNull([Sales / Transfer Qty]), "Yes/No")
Expr1: Format(Not (DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty])), "Yes/No")
/gustav
DateDiff("d", [Last Received Date], Date())/365
My apology to you, Gustav, I never could get the "Format" formulas to work.
Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty]), "Yes/No")
Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And Not IsNull([Sales / Transfer Qty]), "Yes/No")
Expr1: Format(Not (DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty])), "Yes/No")
/gustav
ASKER
Sorry Gozreh, I wanted to date differences to be in years, not days.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The logic is: if two conditions are true:
difference between current date and Last Received Date>= 2 ,
Sales / Transfer Qty is Null
"No", else "Yes".
Then it is, as Format(Not True, "Yes/No") will return "No":
Expr1: Format(Not (DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty])), "Yes/No")
/gustav
difference between current date and Last Received Date>= 2 ,
Sales / Transfer Qty is Null
"No", else "Yes".
Then it is, as Format(Not True, "Yes/No") will return "No":
Expr1: Format(Not (DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty])), "Yes/No")
/gustav
ASKER
Yes, Gozreh, "IIf(Int((Now()-[Last Received Date])/365)>=2 And IsNull([Sales / Transfer Qty]),"No","Yes") " appears to be working but I am validating with the primary user as this is brand new to me as today.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav - I am slow. If the two conditions are true:
"difference between current date and Last Received Date>= 2 ,
Sales / Transfer Qty is Null"
I want "No" to be the answer. Can I not get there without the "Format()?
Thanks.
"difference between current date and Last Received Date>= 2 ,
Sales / Transfer Qty is Null"
I want "No" to be the answer. Can I not get there without the "Format()?
Thanks.
Have you tried the function, it is much simpler than screwing with the IIF statement.
All you have to do is pass the function two values in your query, or in the ControlSource of a control, or in your VBA for that matter.
All you have to do is pass the function two values in your query, or in the ControlSource of a control, or in your VBA for that matter.
> I want "No" to be the answer.
Yes, so that's what the expression returns.
> Can I not get there without the "Format()?
Sure, but why reinvent the wheel? Format is for exactly this.
/gustav
Yes, so that's what the expression returns.
> Can I not get there without the "Format()?
Sure, but why reinvent the wheel? Format is for exactly this.
/gustav
ASKER
My apology for not getting back sooner, but there are some things I cannot control. I appreciate all of your thoughts, and have assigned values accordingly as I know EE wants to get this question "completed". Thanks again. - Tom