+ Reply to Thread
Results 1 to 5 of 5

Trouble with If then to return blank in Access

  1. #1
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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 Capricci

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

    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 Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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 Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    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 Sam Capricci; 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
    Office 365 ProPlus
    Posts
    5,855

    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)

Similar Threads

  1. List of Access database paths in Excel - Need to return Access version
    By Wedge120 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2019, 12:19 PM
  2. [SOLVED] Cells in a range are all not blank, return maximum value (date), If 1 blank return 0
    By Matt1998 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2016, 11:14 AM
  3. Return first non blank cell (cells have formulas that return blank)
    By BG1983 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-05-2016, 04:06 PM
  4. Trouble exporting time from Excel to MS Access
    By AstroZelda in forum Access Tables & Databases
    Replies: 9
    Last Post: 05-20-2015, 11:15 AM
  5. Still Having Trouble with Excel to Access
    By Vladamir in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2012, 12:04 AM
  6. Trouble With Access Relationships
    By keithfender in forum Access Tables & Databases
    Replies: 1
    Last Post: 12-12-2011, 10:26 PM
  7. Calculate data from Access and return to Access
    By Mary Fran in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-17-2006, 10:15 AM

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