Blanks in field-can not modify: IS null,=null, or len identifier not working

[#1]

Every [part number] should have a [Commodity Code] however when the [Commodity Code] is blank, isnull, or [Commodity Code]=null() or if(len([Commodity Code])<2,1,0) doesnt work.

Please see my qvw attached.

[#2]

Hi Joseph,

For the [part numbers] that appear to return no [Commodity Code] in the data, it looks like in fact they are returning 3 spaces instead.

As a result of the rows containing 3 spaces, this is why they are not satisfying the isnull() function or the

if(len([Commodity Code])<2,1,0).

Perhaps try

if([Commodity Code])='   ',1,0)   using 3 spaces in between the ' '

Have attached a table of your data...

Hope this helps?

Kind regards,

Rich

[swuehl · Accepted solution]

Only an idea: If you might have varying number of spaces, you could use one of the trim functions:

=if(trim([Commodity Code])='','BLANK',[Commodity Code])

[#4]

when i used =if(len(trim([Commodity Code]))<2'','BLANK',[Commodity Code])

it worked.  Also, RichSheppard's input was very helpfuul and explains why my original formula didnt work.