Access 2003 VBA Report Query ::Multiply Condition Failure

danfiggolf used Ask the Experts™
If I cut the statement in half, removing the And and all after, I get the dates we need for COMP_TYPE="COM".  The problem is we have 5 COMP_TYPE conditions which we need dates from, and this works if I only do one.  Your help appreciated.
=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

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Hi danfiggolf,

Can you try and explain exactly what you're tring to achieve, maybe with a screenshot?



Hello Jeff -
 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.
 Thanks, Dan

Excuse me, Gary [Not Jeff]

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