Solved

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

Posted on 2014-11-18
27
241 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run Time Error 3071 26 40
Problem with syntax with TransferDatabase 3 15
Access Query based on a Form Field 2 20
Acc 2010 pause execution 10 10
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

25 Experts available now in Live!

Get 1:1 Help Now