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"))
LVL 7
tomfarrarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GozrehCommented:
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull([Sales / Transfer Qty]),"No","Yes"))
0
GozrehCommented:
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"))
0
tomfarrarAuthor Commented:
Wrong number of arguments error with
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2 And IIf(IsNull([Sales / Transfer Qty])),"No","Yes")
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

GozrehCommented:
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"))
0
tomfarrarAuthor Commented:
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".
0
tomfarrarAuthor Commented:
More specifically, items <= Now()-[Last Received Date])/365)>=2 are blank because they were excluded from the first IIF statement (Apparently).
0
tomfarrarAuthor Commented:
I meant Now()-[Last Received Date])/365)<2 are blank because they were excluded from the first IIF statement (Apparently).
0
GozrehCommented:
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")
0
Gustav BrockCIOCommented:
You can use:

Expr1: Format(DateDiff("d", [Last Received Date], Date()) / 365 >= 2 And IsNull([Sales / Transfer Qty]), "Yes/No")

/gustav
0
tomfarrarAuthor Commented:
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".
0
GozrehCommented:
is Sales / Transfer Qty 2 fields or 1 ?
0
Gustav BrockCIOCommented:
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
0
tomfarrarAuthor Commented:
Sales / Transfer Qty  is one field.
0
tomfarrarAuthor Commented:
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".
0
GozrehCommented:
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")
0
GozrehCommented:
difference between current date and Last Received Date>= 2
Int((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())
0
tomfarrarAuthor Commented:
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
0
tomfarrarAuthor Commented:
Sorry Gozreh, I wanted to date differences to be in years, not days.
0
GozrehCommented:
So is this working ?
IIf(Int((Now()-[Last Received Date])/365)>=2 And IsNull([Sales / Transfer Qty]),"No","Yes")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Personally, I would recommend that you write a function and call the function rather than try to understand the complicated logic within an IIF() statement.
Public Function SomeName(dtLastReceived as Date, SalesTransferQty as Variant) as String

   If Int((Now() - dtLastReceived)/365)<2 Then
       'If dtLastReceived is less than two years ago (not entirely accurate due to leap years)
       SomeName = "Yes"
   ElseIf IsNull(SalesTransferQty) Then
       'If dtLastReceived is two or more years ago, and the SalesTransferQty is NULL
       SomeName = "No"
   Else
       SomeName = "Yes"
   End if

End Function

Open in new window

Although this IIF is not that complicated it is much easier to implement business logic like this in a function, which can be reused throughout your application, and easily documented.  The other advantage is that when the business logic changes, you can simply remark out the old version and indicate when (dates) that code was used and just insert the new logic.
0
Gustav BrockCIOCommented:
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
0
tomfarrarAuthor Commented:
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.
0
Gustav BrockCIOCommented:
Note as well, that the way to calculate years is not very precise.
You can use the function below and reduce your expression to:

Expr1: Format(Not (AgeSimple([Last Received Date]) >= 2 And IsNull([Sales / Transfer Qty])), "Yes/No")
Public Function AgeSimple( _
  ByVal datDateOfBirth As Date) _
  As Integer

' Returns the difference in full years from datDateOfBirth to current date.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2007-06-26. Cactus Data ApS, CPH.

  Dim datToday  As Date
  Dim intAge    As Integer
  Dim intYears  As Integer
    
  datToday = Date
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDateOfBirth, datToday)
  If intYears > 0 Then
    ' Decrease by 1 if current date is earlier than birthday of current year
    ' using DateDiff to ignore a time portion of datDateOfBirth.
    intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) > 0)
  End If
  
  AgeSimple = intAge
  
End Function

Open in new window

/gustav
0
tomfarrarAuthor Commented:
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.
0
Dale FyeCommented:
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.
0
Gustav BrockCIOCommented:
> 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
0
tomfarrarAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.