IIF formula

  • Thread starter Thread starter Emily
  • Start date Start date
E

Emily

I have a formula in a query which works fine until I tried to test it by
deleting values in the form. Instead of nothing showing up, like how the
formula should work, it showed ",X"

The formula

Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) & IIf([Other
2]=0,Null,"," & [Other 2] & "X" & [Type 2])

Example:
[Other] = 2
[Type] = 4
[Other 2] = 3
[Type 2] = 5

Should look like 2X4,3X5
When [Other 2] and [Type 2] values are deleted it should look like 2X4
instead of 2X4,X

The default value for the [Other] and [Other 2] fields is 0 on table the
data is coming from.

TIA,
Emily
 
I have a formula in a query which works fine until I tried to test
it by deleting values in the form. Instead of nothing showing up,
like how the formula should work, it showed ",X"

The formula

Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) &
IIf([Other 2]=0,Null,"," & [Other 2] & "X" & [Type 2])

Example:
[Other] = 2
[Type] = 4
[Other 2] = 3
[Type 2] = 5

Should look like 2X4,3X5
When [Other 2] and [Type 2] values are deleted it should look like
2X4 instead of 2X4,X

The default value for the [Other] and [Other 2] fields is 0 on
table the data is coming from.

TIA,
Emily
Click to expand...


the two concatenation operators + and & differ in how they treat
nulls, in that null & string = string, but null + string = null.

Other Fills: IIf([Other]=0,Null,[Other] + "X" + [Type]) & IIf([Other
2]=0,Null,"," + [Other 2] + "X" + [Type 2])
 
I have a formula in a query which works fine until I tried to test it by
deleting values in the form. Instead of nothing showing up, like how the
formula should work, it showed ",X"

The formula

Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) & IIf([Other
2]=0,Null,"," & [Other 2] & "X" & [Type 2])

Example:
[Other] = 2
[Type] = 4
[Other 2] = 3
[Type 2] = 5

Should look like 2X4,3X5
When [Other 2] and [Type 2] values are deleted it should look like 2X4
instead of 2X4,X

The default value for the [Other] and [Other 2] fields is 0 on table the
data is coming from.
Click to expand...

If you erase the form control, then it does not contain a zero any more: the
default value only applies at the moment that a new record is created.

To cover both cases use NZ():

Other Fills: IIf(NZ([Other])=0,Null,[Other] & "X" & [Type]) & IIf(NZ([Other
2])=0,Null,"," & [Other 2] & "X" & [Type 2])


John W. Vinson [MVP]
 
Thank you to both of you. I knew it was something simple that I had not
learned yet!

~ Emily
 
Back
Top