Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Where in this query calculation would I put NZ? I keep getting incorrect number of arguments. I should get 0's but I am getting blanks so I thought I needed NZ.

    Dental Members Cross Sold: IIf([Dental Status]="Gain",IIf([Product Category]="Two or More products",[JanDentalMem],0))

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='LindaR' post='796072' date='02-Oct-2009 13:02']Where in this query calculation would I put NZ? I keep getting incorrect number of arguments. I should get 0's but I am getting blanks so I thought I needed NZ.

    Dental Members Cross Sold: IIf([Dental Status]="Gain",IIf([Product Category]="Two or More products",[JanDentalMem],0))[/quote]

    You have nested IIf statements so both the inner bit and the outer bit need three parts. You are missing the third part of the outer bit. The last part should like this: [JanDentalMem],0),0)

    Which will give you a 0 if Dental Status is not "Gain". You will also get a 0 if Dental Status is "Gain" but Product Category is not "Two or More Products".

    Hopefully I did this right! Hans will correct me if I didn't....

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='PeterN' post='796077' date='02-Oct-2009 18:55']You have nested IIf statements so both the inner bit and the outer bit need three parts. You are missing the third part of the outer bit. The last part should like this: [JanDentalMem],0),0)

    Which will give you a 0 if Dental Status is not "Gain". You will also get a 0 if Dental Status is "Gain" but Product Category is not "Two or More Products".

    Hopefully I did this right! Hans will correct me if I didn't.... [/quote]

    Your solution is probably correct. I used the following and I got the 0s:

    Vision Revenue Cross Sold: IIf([Vision Status]="Gain" And [Product Category]="Two or More products" And ([rcac ucci in]="Y" Or [JanHMIG]="Y"),[Vision Projected Revenue],0)

    At least I hope it is correct. Is the way I did AND with the two OR fields correct? Vision Status = gain and Product category = Two or more products and then either one of the rcac ucci in = Y or JanHMIG = Y then I want vision projected Revenue else a 0.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='LindaR' post='796078' date='02-Oct-2009 13:58']Your solution is probably correct. I used the following and I got the 0s:

    Vision Revenue Cross Sold: IIf([Vision Status]="Gain" And [Product Category]="Two or More products" And ([rcac ucci in]="Y" Or [JanHMIG]="Y"),[Vision Projected Revenue],0)

    At least I hope it is correct. Is the way I did AND with the two OR fields correct? Vision Status = gain and Product category = Two or more products and then either one of the rcac ucci in = Y or JanHMIG = Y then I want vision projected Revenue else a 0.[/quote]

    On the face of it, it looks correct to me. I always consider that if the formula is giving me the answer I want/need then it is correct. In the previous one, you were definitely missing the third part of the outer statement. This one has three parts and your Ors are in brackets, so it should work.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='PeterN' post='796079' date='02-Oct-2009 18:12']On the face of it, it looks correct to me. I always consider that if the formula is giving me the answer I want/need then it is correct. In the previous one, you were definitely missing the third part of the outer statement. This one has three parts and your Ors are in brackets, so it should work.[/quote]


    Thank you for your help.

Posting Permissions

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