?
Solved

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

Posted on 2014-11-18
27
Medium Priority
?
262 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 51

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 51

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 800 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
Dale Fye earned 400 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 51

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 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 800 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
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 51

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses

770 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