Multiple IIf Statements using And in Expression Builder

danfiggolf
danfiggolf used Ask the Experts™
on
I can get the code in the attached snippet to work when I do not use an And in the statement that allows me to look at more then one COM_TYPE value, but when I do the following no dates appear in the "Recent Check" field of the Access reports.  So, If I add this code it doesn't work:

=IIf([COMP_TYPE]="COM",IIf([Date_032] Is Null,IIf([Date_031] Is Null,IIf([Date_030] Is Null,IIf([Date_029] Is Null,[Date_029],[Date_029]),[Date_030]),[Date_031]),[Date_032])) And
 IIf([COMP_TYPE]="MFD",IIf([Date_039] Is Null,IIf([Date_038] Is Null,IIf([Date_037] Is Null,IIf([Date_036] Is Null,[Date_036],[Date_036]),[Date_037]),[Date_038]),[Date_039]))

but, this returns data no problem -

=IIf([COMP_TYPE]="COM",IIf([Date_032] Is Null,IIf([Date_031] Is Null,IIf([Date_030] Is Null,IIf([Date_029] Is Null,[Date_029],[Date_029]),[Date_030]),[Date_031]),[Date_032]))

 I'm trying to retrieve the last date entered as depicted in the snapshots I sent you.  The data is inputted via our Permits application and I'm trying to grab the 1st, 2nd, 3rd, or maybe the forth date entered, if it is the last one entered.  So if it is the 3rd field that was the last used, the forth would be null.  

The code in Slide3.GIF was returning no recent data, then I tried this code and it gives me the dates for COMP_TYPE=COM but if I use "And" I get nothing again.  So the question is how can I have multiple COMP_TYPE(s) using And?

 See attached snapshots.
=IIf([COMP_TYPE]="COM",IIf([Date_032] Is Null,IIf([Date_031] Is Null,IIf([Date_030] Is Null,IIf([Date_029] Is Null,[Date_029],[Date_029]),[Date_030]),[Date_031]),[Date_032])) And
 IIf([COMP_TYPE]="MFD",IIf([Date_039] Is Null,IIf([Date_038] Is Null,IIf([Date_037] Is Null,IIf([Date_036] Is Null,[Date_036],[Date_036]),[Date_037]),[Date_038]),[Date_039]))

Open in new window

Slide1.GIF
Slide2.GIF
Slide3.GIF
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
if I remember correctly, in the expression builder you need to use the functional syntax of and:


IIF(AND(condition1, condition2), value_if_true, value_if_false)

Open in new window

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
FWIW,

Not a specific solution, but advice.

When your Form Control Functions get this complex and have this many nesting levels, IMHO, it is time to start creating Functions in code.

The If-Then-Else Construct in code is much easire to read, understand and troubleshoot.

Just my 2c

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
AngelIII,

That Sytax "=IF(AND(Cond1,Cond2,...),True,False)" is more for IF() ...like in Excel
(if you use it with IIF you will get a Syntax error)

IIF can have the "AND" inserted using the more literal sysnax:
    =IIf([Price]>15 And [Rate]=0.06,"Too High","Too Low")
;-)

Jeff

(Shave the Goatee yet?)
;-)
Commented:
The And you're writing is after the End of the first If statement so it's irrelevent from a syntax point of view, and it's also irrelevent from a logical point of view, because either COMP_TYPE=COM or COMP_TYPE=MFD.

I don't know if I understood what you want to do exactly, but I can suggest the following:

=IIf([COMP_TYPE]="COM",IIf([Date_032] Is Null,IIf([Date_031] Is Null,IIf([Date_030] Is Null,IIf([Date_029] Is Null,[Date_029],[Date_029]),[Date_030]),[Date_031]),[Date_032]),IIf([COMP_TYPE]="MFD", IIf([Date_039] Is Null,IIf([Date_038] Is Null,IIf([Date_037] Is Null,IIf([Date_036] Is Null,[Date_036],[Date_036]),[Date_037]),[Date_038]),[Date_039])))

The test if COMP_TYPE="MFD" is carried if COMP_TYPE not = "COM"


 IIf([COMP_TYPE]="MFD",)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial