NZ function Access 2003

Topic

New Reply

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))

Reply | Quote

Replies

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))

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….

Reply | 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….

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.

Reply | 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.

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.

Reply | 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.

Thank you for your help.

Reply | Quote