none
IIF Question RRS feed

  • Question

  • I have a report with a query that follows.

    SELECT Training_Documents_Parent.Training_Doc_ID, Training_Documents_Parent.Training_Doc_No, Training_Documents_Parent.Training_Doc_Rev, Training_Documents_Parent.Rev_Date, Training_Documents_Parent.Training_Doc_Name, VisualMFGAbbreviationsChild.VisualMFGAbbreviation
    FROM Training_Documents_Parent LEFT JOIN VisualMFGAbbreviationsChild ON Training_Documents_Parent.Training_Doc_ID = VisualMFGAbbreviationsChild.Training_Doc_ID
    ORDER BY Training_Documents_Parent.Training_Doc_ID;

    The query works as I expect it too.

    I have a text box with a control source as follows:

    =IIf(IsNull([VisualMFGAbbreviation]=True),"None",[VisualMFGAbbreviation])

    The control is populated with "None" when the value is Null, but I get "#Type when there is a not null value.  Any ideas?

    Tuesday, October 29, 2019 2:31 PM

Answers

  • One closing parenthesis is misplaced:

    =IIf(IsNull([VisualMFGAbbreviation])=True,"None",[VisualMFGAbbreviation])

    You can simplify this to

    =IIf(IsNull([VisualMFGAbbreviation]),"None",[VisualMFGAbbreviation])

    or you can use the Nz function:

    =Nz([VisualMFGAbbreviation],"None")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tkosel Tuesday, October 29, 2019 3:33 PM
    Tuesday, October 29, 2019 2:43 PM

All replies

  • One closing parenthesis is misplaced:

    =IIf(IsNull([VisualMFGAbbreviation])=True,"None",[VisualMFGAbbreviation])

    You can simplify this to

    =IIf(IsNull([VisualMFGAbbreviation]),"None",[VisualMFGAbbreviation])

    or you can use the Nz function:

    =Nz([VisualMFGAbbreviation],"None")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tkosel Tuesday, October 29, 2019 3:33 PM
    Tuesday, October 29, 2019 2:43 PM
  • Hans,

    Thanks for your response.  I could have sworn I had all the stuff right.  All you suggestions are useful to me.

    Tuesday, October 29, 2019 3:33 PM