Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: IIF statement not working

    I'm having an issue, my report pulls a date field and based on that date field I'm hard coding a plan end date for a benefit. The IIF statement goes through the first three IIF statements but then stops working and it never gets to the final else command because that field on the final output is occasionally left blank. The code is below, where I've added a couple of extra spaces, that is the part that isn't working. There are October 09 dates but it's leaving them blank. Thanks in advance for any help.

    SELECT ELECTION_LETTER3.EVENT, ELECTION_LETTER3.SSN, ELECTION_LETTER3.FULLSSN, ELECTION_LETTER3.RELATE_NUM, ELECTION_LETTER3.LNAME, ELECTION_LETTER3.FNAME, ELECTION_LETTER3.STREET, ELECTION_LETTER3.SUITE, ELECTION_LETTER3.CITY, ELECTION_LETTER3.STATE, ELECTION_LETTER3.ZIP, ELECTION_LETTER3.DOB, ELECTION_LETTER3.TRANSTATUS, ELECTION_LETTER3.TRANCHGDAT, ELECTION_LETTER3.DATEADD, ELECTION_LETTER3.QUALCD, ELECTION_LETTER3.QUALDT, ELECTION_LETTER3.QUALDATE, ELECTION_LETTER3.NTFYDT, ELECTION_LETTER3.TERMDT, ELECTION_LETTER3.TERMCD, ELECTION_LETTER3.COBRAEFFTDT, ELECTION_LETTER3.DTTRMD, ELECTION_LETTER3.PROMDL, ELECTION_LETTER3.CONO, ELECTION_LETTER3.EMPRNAME, ELECTION_LETTER3.DIVSNAME, ELECTION_LETTER3.STATUS, ELECTION_LETTER3.COVREFFTDT, ELECTION_LETTER3.EXPDATE, ELECTION_LETTER3.ICID, ELECTION_LETTER3.CODE, ELECTION_LETTER3.DESCR, ELECTION_LETTER3.ELIG, ELECTION_LETTER3.TIERDESC, ELECTION_LETTER3.CARRIERPLAN, ELECTION_LETTER3.PLANDESC, ELECTION_LETTER3.EFFSDT, ELECTION_LETTER3.EFFEDT, ELECTION_LETTER3.AMT, ELECTION_LETTER3.ADMFEE, ELECTION_LETTER3.ADMINFEE, CCur([ELECTION_LETTER3.AMT]*[ELECTION_LETTER3.ADMINFEE]) AS ADMINFEEAMT, (([ELECTION_LETTER3.AMT]*[ELECTION_LETTER3.ADMINFEE])+[ELECTION_LETTER3.AMT]) AS PREMIUM, CCur(((([ELECTION_LETTER3.AMT]*[ELECTION_LETTER3.ADMINFEE])+[ELECTION_LETTER3.AMT]))*0.35) AS SUBSIDY, ELECTION_LETTER3.ARRA, ELECTION_LETTER3.REINST, IIF((ELECTION_LETTER3.REINST)="N",ELECTION_LETTER3 .QUALDATE,
    IIF((ELECTION_LETTER3.REINST)="1" AND MID((ELECTION_LETTER3.QUALDATE),9,2)="08", SWITCH(MID((ELECTION_LETTER3.QUALDATE),1,2)="09"," 09/30/2008",MID((ELECTION_LETTER3.QUALDATE),1,2)="10","1 0/31/2008",MID((ELECTION_LETTER3.QUALDATE),1,2)="11","1 1/30/2008",MID((ELECTION_LETTER3.QUALDATE),1,2)="12","1 2/31/2008"), IIF((ELECTION_LETTER3.REINST)="1" AND MID((ELECTION_LETTER3.QUALDATE),9,2)="09", SWITCH(MID((ELECTION_LETTER3.QUALDATE),1,2)="01"," 01/31/2009",MID((ELECTION_LETTER3.QUALDATE),1,2)="02","0 2/28/2009",MID((ELECTION_LETTER3.QUALDATE),1,2)="03","0 3/31/2009",MID((ELECTION_LETTER3.QUALDATE),1,2)="04","0 4/30/2009",MID((ELECTION_LETTER3.QUALDATE),1,2)="05","0 5/31/2009",MID((ELECTION_LETTER3.QUALDATE),1,2)="06","0 6/30/2009",MID((ELECTION_LETTER3.QUALDATE),1,2)="07","0 7/31/2009",MID((ELECTION_LETTER3.QUALDATE),1,2)="08","0 8/31/2009", MID((ELECTION_LETTER3.QUALDATE),1,2)="09","09/30/2009"),


    IIF((ELECTION_LETTER3.REINST)="1" AND MID((ELECTION_LETTER3.QUALDATE),9,2)="09", SWITCH(MID((ELECTION_LETTER3.QUALDATE),1,2)="10"," 10/31/2009",MID((ELECTION_LETTER3.QUALDATE),1,2)="11","1 1/30/2009", MID((ELECTION_LETTER3.QUALDATE),1,2)="12","12/31/2009"),ELECTION_LETTER3.ADMINFEE)))) AS EXPLTRDT INTO ELECTION_LETTER_DATA
    FROM ELECTION_LETTER3;

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    IIF()'s don't allow for infinite levels of nesting.

    Looks to me like you've got some SERIOUS design issues here though. Why do you have so many pieces of data coded in to the same field?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2009
    Posts
    4
    I was able to get it to run, there was an extra character. I don't know a better way to go about it to get the end results I want. I have a date field (e.g. 10/16/2009) and basically if there are certain perameters I need this date to be 10/31/09. I didn't know of a better way to do it other than pull out the year first, and then do a switch based on the month number, any suggestion on how to do this more efficiently?

    But back to this query, as soon as I added another iif to convert the 2010 dates I got a query to complex error.

    SELECT ELECTION_LETTER3.EVENT, ELECTION_LETTER3.SSN, ELECTION_LETTER3.FULLSSN, ELECTION_LETTER3.RELATE_NUM, ELECTION_LETTER3.LNAME, ELECTION_LETTER3.FNAME, ELECTION_LETTER3.STREET, ELECTION_LETTER3.SUITE, ELECTION_LETTER3.CITY, ELECTION_LETTER3.STATE, ELECTION_LETTER3.ZIP, ELECTION_LETTER3.DOB, ELECTION_LETTER3.TRANSTATUS, ELECTION_LETTER3.TRANCHGDAT, ELECTION_LETTER3.DATEADD, ELECTION_LETTER3.QUALCD, ELECTION_LETTER3.QUALDT, ELECTION_LETTER3.QUALDATE, ELECTION_LETTER3.NTFYDT, ELECTION_LETTER3.TERMDT, ELECTION_LETTER3.TERMCD, ELECTION_LETTER3.COBRAEFFTDT, ELECTION_LETTER3.DTTRMD, ELECTION_LETTER3.PROMDL, ELECTION_LETTER3.CONO, ELECTION_LETTER3.EMPRNAME, ELECTION_LETTER3.DIVSNAME, ELECTION_LETTER3.STATUS, ELECTION_LETTER3.COVREFFTDT, ELECTION_LETTER3.EXPDATE, ELECTION_LETTER3.ICID, ELECTION_LETTER3.CODE, ELECTION_LETTER3.DESCR, ELECTION_LETTER3.ELIG, ELECTION_LETTER3.TIERDESC, ELECTION_LETTER3.CARRIERPLAN, ELECTION_LETTER3.PLANDESC, ELECTION_LETTER3.EFFSDT, ELECTION_LETTER3.EFFEDT, ELECTION_LETTER3.AMT, ELECTION_LETTER3.ADMFEE, ELECTION_LETTER3.ADMINFEE, CCur([ELECTION_LETTER3.AMT]*[ELECTION_LETTER3.ADMINFEE]) AS ADMINFEEAMT, (([ELECTION_LETTER3.AMT]*[ELECTION_LETTER3.ADMINFEE])+[ELECTION_LETTER3.AMT]) AS PREMIUM, CCur(((([ELECTION_LETTER3.AMT]*[ELECTION_LETTER3.ADMINFEE])+[ELECTION_LETTER3.AMT]))*0.35) AS SUBSIDY, ELECTION_LETTER3.ARRA, ELECTION_LETTER3.REINST, IIF((ELECTION_LETTER3.REINST)="N",ELECTION_LETTER3 .QUALDATE,

    IIF((ELECTION_LETTER3.REINST)="1" AND MID((ELECTION_LETTER3.QUALDATE),9,2)="09",

    SWITCH
    (MID((ELECTION_LETTER3.QUALDATE),1,2)="01","01/31/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="02","02/28/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="03","03/31/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="04","04/30/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="05","05/31/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="06","06/30/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="07","07/31/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="08","08/31/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="09","09/30/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="10","10/31/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="11","11/30/2009",
    MID((ELECTION_LETTER3.QUALDATE),1,2)="12","12/31/2009"),

    ELECTION_LETTER3.ADMINFEE)) AS EXPLTRDT INTO ELECTION_LETTER_DATA
    FROM ELECTION_LETTER3;

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Looks like you could use a "QUALDATES" lookup table...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    It looks far too convoluted and can probably be acheived a lot simpler than this.

    IIF((ELECTION_LETTER3.REINST)="1" AND MID((ELECTION_LETTER3.QUALDATE),9,2)="09"

    <If this is a date field, wouldnt it be more appropriate to use DatePart - I'm guessing you are just checking the date is a certain day / month?

    MID((ELECTION_LETTER3.QUALDATE),1,2)="01","01/31/2009",

    <Again, can you not use DateAdd to derrive the last day of the month from QUALDATE (DateAdd a month then DateAdd -1 day? = Last day of month)

    Can you explain in plain English what you are trying to acheive here;

    Is the issue that EXPLTRDT has to be a certain format in the ELECTION_LETTER_DATA table - Why so?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •