IIF statements

  • Thread starter Thread starter ddoblank
  • Start date Start date
D

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.
 
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.
Click to expand...

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

Rick Brandt said:
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.
Click to expand...

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?
Click to expand...
 
Back
Top