Results 1 to 11 of 11
  1. #1
    Join Date
    May 2012
    Posts
    18

    Unanswered: iif(isnull) Function

    Hello there.

    I am getting an invalid error for the syntax below
    Is =iif(isnull(combo box name in form,qry field Description),,)

    Is it possible to use IIF and ISNULL together in an unbound text field and use that expression to get a data from a query?

    I have attached a screenshot of what I am doing.

    So basically there are different levels: I, II, III and each has their own description. sometimes Level II and III will be null so I would use the description of Level I but if those combo box are NOT NULL, i will be using Description of Level II or III.

    I would be happy if you could guide me through it ^_^
    Attached Thumbnails Attached Thumbnails access.JPG  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by undee69 View Post
    Hello there.

    I am getting an invalid error for the syntax below
    Is =iif(isnull(combo box name in form,qry field Description),,)

    Is it possible to use IIF and ISNULL together in an unbound text field and use that expression to get a data from a query?
    It surely is, provided that you respect the correct syntax:

    1. Is is a reserved keyword and should not be used for naming a variable.

    2. IsNull is a function that accepts a single argument, so this:
    Code:
    isnull(combo box name in form,qry field Description)
    is incorrect (2 arguments here).

    3. You must supply 3 arguments to the IIf function and none of these is optional, so this is also incorrect:
    Code:
    iif(isnull(...),,)
    because the second and third arguments are missing.

    The correct syntax is:
    Code:
    Result = IIf(IsNull(Expression), ValueWhenExpressionIsNull, ValueWhenExpressionIsNotNull)
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    18
    Thanks Sinndho

    Before i made this post, I first tried isnull function in the unbound text field: =isnull(cbolvl2 (my level2 combo box), [L1L2_1].[tblL1.Description] (my query field)); but it is giving me the error: "the expression you entered has a function containing the wrong number of arguments." that's why i tried using iif function. but using the isnull function in the unbound is not working for me either.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by undee69 View Post
    Thanks Sinndho

    Before i made this post, I first tried isnull function in the unbound text field: =isnull(cbolvl2 (my level2 combo box), [L1L2_1].[tblL1.Description] (my query field)); but it is giving me the error: "the expression you entered has a function containing the wrong number of arguments." that's why i tried using iif function. but using the isnull function in the unbound is not working for me either.
    I suppose that "(my level2 combo box)" and "(my query field)" are comments you added to the actual expression you tried to use, so this expression probably was:
    Code:
    =isnull(cbolvl2, [L1L2_1].[tblL1.Description]))
    Which is incorrect too, and for the same reasons:
    1. You try to pass 2 arguments to the Isnull function:
    Code:
    isnull(cbolvl2, [L1L2_1].[tblL1.Description])
    2. The second and third arguments for the IIf function are missing.
    Code:
    =isnull(cbolvl2, [L1L2_1].[tblL1.Description]), <Missing second argument>, <Missing third argument>)
    Please post the complete code that causes problem as it is, with some explanations, if necessary, outside the code.
    Have a nice day!

  5. #5
    Join Date
    May 2012
    Posts
    18
    The code as is on the unbound text field:
    =IIf(IsNull([cbolvl2]),[L1L2_1].[tblL1.Description],[Copy Of qryAllLevel].[L1L2_1].[level 2])

    I have level I, II and III account. Each level has its own description, but Level II is a sublevel of I and Level III is sublevel of II. If Level II and III are blank on the combobox, I will be using the description of level I. If level III is blank, I will be using Level II Description. If all levels are filled in, I will be using Level III description.

    Thank you for your patience with me ^_^

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by undee69 View Post
    The code as is on the unbound text field:
    =IIf(IsNull([cbolvl2]),[L1L2_1].[tblL1.Description],[Copy Of qryAllLevel].[L1L2_1].[level 2])
    I suppose that cbolvl2 is the name of a combo, which seems correct. However:

    1. [tblL1.Description] is not correct: When you use a multi-part identifier (or reference), such as Table.Column or Form.Control, you must enclose each part in its own pair of square brackets if you use them: [Table].[Column] or [Form].[Control].

    2. In these (corrected) multi-part references:
    Code:
    [L1L2_1].[tblL1].[Description]
    and
    Code:
    [Copy Of qryAllLevel].[L1L2_1].[level 2]
    What are [L1L2_1], [tblL1], [level 2], [Copy Of qryAllLevel] and [level 2] (i.e. which objects in the database do they represent)?

    3. Do you try to use this expression in a VBA procedure or as the property of a control?

    Quote Originally Posted by undee69 View Post
    Thank you for your patience with me ^_^
    You're welcome
    Have a nice day!

  7. #7
    Join Date
    May 2012
    Posts
    18
    I tried that a while ago. It is showing " #NAME? "

    [L1L2_1] < Query Name
    [tblL1] < Table name
    [Copy Of qryAllLevel] < query of Levels I, II and III with corresponding description and concatenation
    [level 2] < Field in [Copy Of qryAllLevel]


    I am using the Control property..

    Would you mind if I attach my db, then maybe you could assist me ^_^

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by undee69 View Post
    IWould you mind if I attach my db, then maybe you could assist me ^_^
    Do so, it will be easier.
    Have a nice day!

  9. #9
    Join Date
    May 2012
    Posts
    18
    Quote Originally Posted by Sinndho View Post
    Do so, it will be easier.
    attached is my db
    Attached Files Attached Files

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are two forms in the database you posted: frmPaymentSheet_rev1 and Form1. None of these has a control trying to use an expression such as the one you mentioned.
    Have a nice day!

  11. #11
    Join Date
    May 2012
    Posts
    18
    Quote Originally Posted by Sinndho View Post
    There are two forms in the database you posted: frmPaymentSheet_rev1 and Form1. None of these has a control trying to use an expression such as the one you mentioned.
    Good day Sinndho.

    It's in the frmPaymentSheet_rev, Description.

    What I would be doing is that, since Level II sublevel/dependent on Level I and Level III is sublevel/dependent on Level II, if I select something on the "parent"combobox and that combo box has no sublevel, it will be null, and it will display on the Description Label/Text the Level(s) that it correpond.

    I am not sure if this is gonna work with Access though. I am new to this programming really.Been checking sites and forum on how to do it.
    Attached Thumbnails Attached Thumbnails access_1.JPG   payment_sheet.JPG  

Posting Permissions

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