Solved

Access 2010 Formula of "IFF" and "AND" Error

Posted on 2014-11-18
27
254 Views
Last Modified: 2014-11-23
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"))
0
Comment
Question by:tomfarrar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
  • 5
  • +1
27 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 40450289
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2,IIf(IsNull([Sales / Transfer Qty]),"No","Yes"))
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 40450297
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450299
Wrong number of arguments error with
Expr1: IIf(Int((Now()-[Last Received Date])/365)>=2 And IIf(IsNull([Sales / Transfer Qty])),"No","Yes")
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Gozreh
ID: 40450303
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450304
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450317
More specifically, items <= Now()-[Last Received Date])/365)>=2 are blank because they were excluded from the first IIF statement (Apparently).
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450322
I meant Now()-[Last Received Date])/365)<2 are blank because they were excluded from the first IIF statement (Apparently).
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 40450323
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40450334
You can use:

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

/gustav
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450353
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
 
LVL 10

Expert Comment

by:Gozreh
ID: 40450359
is Sales / Transfer Qty 2 fields or 1 ?
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40450368
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450412
Sales / Transfer Qty  is one field.
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450416
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
 
LVL 10

Expert Comment

by:Gozreh
ID: 40450433
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
 
LVL 10

Expert Comment

by:Gozreh
ID: 40450649
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450668
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40450675
Sorry Gozreh, I wanted to date differences to be in years, not days.
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 200 total points
ID: 40450684
So is this working ?
IIf(Int((Now()-[Last Received Date])/365)>=2 And IsNull([Sales / Transfer Qty]),"No","Yes")
0
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 40450901
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40451059
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40451067
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
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 total points
ID: 40451118
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
 
LVL 7

Author Comment

by:tomfarrar
ID: 40451136
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40451162
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40451703
> 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
 
LVL 7

Author Closing Comment

by:tomfarrar
ID: 40461018
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question