+ Reply to Thread
Results 1 to 5 of 5

Trouble with If then to return blank in Access

  1. #1
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    5,103

    Trouble with If then to return blank in Access

    I have been using this formula in a query in access...
    PHP Code: 
    IIf(Left([New PRISM]![Severity Level (Actual)],1)="A","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="B","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="C","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="D","A-D","E-I")))) 
    and until now it was working fine, that is until I noticed it is populating E-I when the severity is blank.
    So I changed it to
    PHP Code: 
    IIf([New PRISM]![Severity Level (Actual)]="","",IIf(Left([New PRISM]![Severity Level (Actual)],1)="A","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="B","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="C","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="D","A-D","E-I"))))) 
    but it still returns E-I when severity is blank.
    then I tried
    PHP Code: 
    IIf([New PRISM]![Severity Level (Actual)]=NULL,NULL,IIf(Left([New PRISM]![Severity Level (Actual)],1)="A","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="B","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="C","A-D",IIf(Left([New PRISM]![Severity Level (Actual)],1)="D","A-D","E-I"))))) 
    and yet again E-I is returned (and I tried ="",NULL and =NULL,"" but they also didn't work and tried inserting it before the final E-I part and still not working).

    So I'm down to three options,
    Option 1, someone here sees what is wrong with the formula that I'm missing.
    Option 2, I extend out the IF Then statements to include all those in E, F, G, H and I then leave a blank.
    Option 3 (easiest) I simply leave the column blank and do the formula in excel.

    Anyone see why my formula is failing when severity is blank?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam C

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,896

    Re: Trouble with If then to return blank in Access

    Personally I'd use SWITCH() rather than multiple nested Iif().

    Ex:
    Please Login or Register  to view this content.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    5,103

    Re: Trouble with If then to return blank in Access

    Interesting, I wondered if it could be consolidated like that in Access but never tried it. I'll give it a try. I also didn't try =TRUE,NULL, I'll give that a try too.
    thanks.

  4. #4
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office Professionsl Plus 2016- work; 2012 for Mac at home
    Posts
    5,103

    Re: Trouble with If then to return blank in Access

    CK76, this is what I adjusted it to and it works fine.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thanks!
    But, I am still missing what was wrong with my formula or tweaks.
    Last edited by Sambo kid; 05-10-2019 at 02:07 PM.

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    3,896

    Re: Trouble with If then to return blank in Access

    You are welcome and thanks for the rep

    I think what was missing in your formula is IsNULL([Column])=TRUE. This is standard method for checking null in MS Access.

    Most other db, you can use ... [Column] Is null ... or COALESCE([Column],Value When Null).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1