IIF statements

[ddoblank]

Can I put two different IIF statements into one field? example, I want to
change a IsNull to a 0 value and I want to change "Surplus" into a value from
another field. This is what I came up with but I keep getting an invalid
syntax error message.

Prev Ordered: IIf(IsNull([Order Qty]),"0",[Order Qty]), IIF([Order
Qty]="SURPLUS",[Qty Recvd])

If I remove either IIF statement it works perfectly but then I am either
left with a null value or the word surplus in my field.

 

[Rick Brandt]

Can I put two different IIF statements into one field? example, I want
to change a IsNull to a 0 value and I want to change "Surplus" into a
value from another field. This is what I came up with but I keep getting
an invalid syntax error message.

Prev Ordered: IIf(IsNull([Order Qty]),"0",[Order Qty]), IIF([Order
Qty]="SURPLUS",[Qty Recvd])

If I remove either IIF statement it works perfectly but then I am either
left with a null value or the word surplus in my field.

First IsNull() should not be used in queries since the SQL "Is Null"
works fine without making a VBA function call. Second you need to use
the second IIf() inside of the first (nested)...

Prev Ordered: IIf([Order Qty] Is Null, "0", IIF([Order Qty]="SURPLUS",
[Qty Recvd], [Order Qty]))

I must say though that it is highly unusual for a "qty" field to be text
and to sometimes contain words. Do you never need to perform arithmetic
on these values?

 

[ddoblank]

Hi Rick, Thank you for your help with this. The first comment on the
placement of IsNull, I just took that out of the Access Help system, that is
how it is shown there but I will certainly make the changes as you suggested
below. When I first designed the database, I didn't know a lot about many of
the functions and I made it work the best I could. Fortunately, I do not have
to perform any arithmetic functions on this column so it hasn't caused any
problems yet. Your expression works prefectly. Thanks again for your help.
Darren

Can I put two different IIF statements into one field? example, I want
to change a IsNull to a 0 value and I want to change "Surplus" into a
value from another field. This is what I came up with but I keep getting
an invalid syntax error message.

Prev Ordered: IIf(IsNull([Order Qty]),"0",[Order Qty]), IIF([Order
Qty]="SURPLUS",[Qty Recvd])

If I remove either IIF statement it works perfectly but then I am either
left with a null value or the word surplus in my field.

First IsNull() should not be used in queries since the SQL "Is Null"
works fine without making a VBA function call. Second you need to use
the second IIf() inside of the first (nested)...

Prev Ordered: IIf([Order Qty] Is Null, "0", IIF([Order Qty]="SURPLUS",
[Qty Recvd], [Order Qty]))

I must say though that it is highly unusual for a "qty" field to be text
and to sometimes contain words. Do you never need to perform arithmetic
on these values?