Solved

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

Posted on 2014-11-18
27
248 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 49

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 49

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 47

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 49

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 49

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 47

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 49

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now