By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

IIF Statement with a null condition

P: 2
Hello,

I am trying to create an IF Statement that checks if a field has a null value.

Table mats by sku is a table with skus and 2 columns of data called Index and Category Index

Index is tied to the sku's material. Problem is, there is not always a material associated with the sku. Therefore we have to look at the product and say, "is this a broom or a watermelon?"

If we do not know if the sku has cedar or oak material, then we look to see if it is a broom, shovel, watermelon, ice cream cone, etc..."

If it is a broom, we match it with the 'broom category index'

So here's my statement in my maketable query

Main Index: IIf([mats by sku]![Material Index]=Null,[mats by sku]![Category Index],[mats by sku]![Material Index])

Basically I am trying to ask it to look at the Material Index field. If it is null for sku 55555, then I want it to take the value in the Category Index column. If the Material Index field is not null then I want it take the value from the Material Index field.

I tried messing around with

= 'null'
= "null"
= isnull

and simply

IIf([mats by sku]![Material Index]isnull ...........

IIf(isnull[mats by sku]![Material Index] .............

It must be something simple I am missing.

The query runs, but when finished, I examine the table and it left the cells with no Material Index value blank and it left the cells with a Material index with the mateirla index value as desired for that piece of the formula. It doesn't seem to recognize the NULL I enter.






Thank you for your time.
May 9 '07 #1
Share this Question
Share on Google+
2 Replies


JConsulting
Expert 100+
P: 603
Hello,

I am trying to create an IF Statement that checks if a field has a null value.

Table mats by sku is a table with skus and 2 columns of data called Index and Category Index

Index is tied to the sku's material. Problem is, there is not always a material associated with the sku. Therefore we have to look at the product and say, "is this a broom or a watermelon?"

If we do not know if the sku has cedar or oak material, then we look to see if it is a broom, shovel, watermelon, ice cream cone, etc..."

If it is a broom, we match it with the 'broom category index'

So here's my statement in my maketable query

Main Index: IIf([mats by sku]![Material Index]=Null,[mats by sku]![Category Index],[mats by sku]![Material Index])

Basically I am trying to ask it to look at the Material Index field. If it is null for sku 55555, then I want it to take the value in the Category Index column. If the Material Index field is not null then I want it take the value from the Material Index field.

I tried messing around with

= 'null'
= "null"
= isnull

and simply

IIf([mats by sku]![Material Index]isnull ...........

IIf(isnull[mats by sku]![Material Index] .............

It must be something simple I am missing.

The query runs, but when finished, I examine the table and it left the cells with no Material Index value blank and it left the cells with a Material index with the mateirla index value as desired for that piece of the formula. It doesn't seem to recognize the NULL I enter.






Thank you for your time.

the syntax you're using can be better shown using the NZ (no Zero) command.

nz([yourfield],"what you want it to be if it's 0")
or

iif([yourfield) is null, true part, false part)

or

iif( is null([yourfield], true part, false part)
May 9 '07 #2

NeoPa
Expert Mod 15k+
P: 31,426
...
Expand|Select|Wrap|Line Numbers
  1. Main Index: IIf([mats by sku]![Material Index]=Null,[mats by sku]![Category Index],[mats by sku]![Material Index])
...
In your example you need :
Expand|Select|Wrap|Line Numbers
  1. Main Index: Nz([mats by sku]![Material Index],[mats by sku]![Category Index])
May 15 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.