Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested IIF function (Access 2000)

    I have the following NZ function :
    UPDATE Products SET items = NZ(items,0) + " & Nz(Quantity, 0)

    This function sets the null values to zeroes for computational purposes.

    I want to test this NZ function whether or not the function was invoked(if the value was null you get i True).It will mean that i will exit the function if item are 0.
    I want to do it with a nested IIF function,so at least
    i have read that one could do it,but i coudnt make it.The function i have read in the book is the following:
    Iif(Nz(MyVal)="", "No Value",MyVal)
    I wonder how cani apply it in my function? If i succeed i will have a very professional code indeed.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested IIF function (Access 2000)

    Perhaps you need to use the IsNull function instead of Nz. The difference is as follows:

    Nz(A, [img]/forums/images/smilies/cool.gif[/img] returns A, unless A is null; in that case it returns B.
    IsNull(A) returns True is A is null, False otherwise.

    Nz acts like a combination of IIf and IsNull: Nz(A, [img]/forums/images/smilies/cool.gif[/img] is equivalent to IIf(IsNull(A), B, A)

    Apart from that, I don't understand what you are asking.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IIF function (Access 2000)

    Thank you veryy much ! My question was realy muddled and therefore you were very right to tell me politely that you do not
    understand what i am asking.But your exlanation was wonderful

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IIF function (Access 2000)

    In my update clause using the NZ function, i would like to add
    a condition to exclude substracting from a Null.I do not want the NULL items to be included in the UPDATE, and then i will have to add a WHERE clause to the SQL to exclude the NULLs. Im i right in principle, and if so, where should one place the where condition in my clause ?


    SQL = "UPDATE Products SET items = NZ(items,0) - " & Nz(Quantity, 0) & strWhere



    strCondition = "ProductID=" & MySubform.Productid
    strWhere = " WHERE " & strCondition
    quantity = MySubform.quantity

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested IIF function (Access 2000)

    Try this:

    strCondition = "ProductID=" & MySubform.Productid & " And Items Is Not Null"

Posting Permissions

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