none
Code help for IIF with AND RRS feed

  • Question

  • there is likely a better way using a loop, but regardless,I have a subform that I want to test if certain fields have null values (about 5 of them). and if they have null values, then I want a label to show "No data".

    here is my dodgy code that is not working....

    
    IIf(IsNull(Me.INWithdrawalRequestDate) AND (IsNull(Me.INWithdrawalRequestAppAssessment)),Me.LabelNoData.Visible = True, Me.LabelNoData.Visible = False)

    Any help with fixing this or providing some other solution would be appreciated.

    Thanks

    John


    JG

    Friday, September 14, 2018 2:41 AM

Answers

  • You don't need to call the IIF function.  Put the following in the form's Current event procedure:

    Me.LabelNoData.Visible = _
        IsNull(Me.INWithdrawalRequestDate) AND _
        IsNull(Me.INWithdrawalRequestAppAssessment) AND _
        IsNull(…………and so on

    If you want to enter data in the controls you'll need some way of making them visible again.

    Ken Sheridan, Stafford, England

    Friday, September 14, 2018 2:29 PM

All replies

  • Hi JG,

    Just to clarify, did you want to check about five fields within the same record to see if all of them are null?

    Friday, September 14, 2018 3:58 AM
  • Yes mate. Same record, five fields on the one form, and if all of those five fields are null, display a label.


    JG

    Friday, September 14, 2018 8:23 AM
  • You don't need to call the IIF function.  Put the following in the form's Current event procedure:

    Me.LabelNoData.Visible = _
        IsNull(Me.INWithdrawalRequestDate) AND _
        IsNull(Me.INWithdrawalRequestAppAssessment) AND _
        IsNull(…………and so on

    If you want to enter data in the controls you'll need some way of making them visible again.

    Ken Sheridan, Stafford, England

    Friday, September 14, 2018 2:29 PM
  • Yes mate. Same record, five fields on the one form, and if all of those five fields are null, display a label.


    JG

    Hi John,

    As Ken said, I hope you're using the form's Current event. You could also try the following:

    Me.LabelNoData.Visible = IsNull(Me.INWithdrawalRequestDate & Me.INWithdrawalRequestAppAssessment & etc.)

    Hope it helps...

    Friday, September 14, 2018 2:59 PM
  • Many thanks (again) Ken. You are a star, I am learning so much from your answers.
    It is a report rather than a form and the code will go in the onformat event of the detail , so I think I am ok with not having to make them visible again, but will test and work through it.

    Thanks again.


    JG

    Friday, September 14, 2018 10:26 PM
  • No offense meant to you .theDBGuy, I appreciate your assistance as well. I am about to review your blog this afternoon for more inspiration and knowledge.

    :)


    JG

    Friday, September 14, 2018 10:28 PM
  • No offense meant to you .theDBGuy, I appreciate your assistance as well. I am about to review your blog this afternoon for more inspiration and knowledge.

    :)


    JG

    Hi JG,

    None taken. Glad we could assist. Good luck with your project.

    Saturday, September 15, 2018 2:01 PM