iif func for null value

Question

i have a table consist of two order no, one is PL and other is UnifiedTo, where PL is the OLD no and Unifiedto is the new modified no. some nos are modified and some are unchanged. i want to convert all no in a single new field.

i use a iif formula in a new field [field 3]  such that :

Expr1: IIf([UnifiedTo] Is Not Null,[UnifiedTo],[PL])

but in the results some fields in which [UnifiedTo] is null is not showing any result, although [PL] have some value. i cross checked the database but not solve this problem. 

although where [UnifiedTo] is not null, result shows perfectly.

Answer 1

It is text field.... Can i use any other func except null

There is no built-in function to determine whether a field is either Null or composed of characters that are non-displayable.  However, you could write such a function, called maybe IsBlank(), or construct an expression using the Like operator.

It's not normally a good thing to have non-displayable, non-space characters in a text field, because they can be confusing.  So it's probably better to find them and get rid of them, if you can, rather than modify your regular queries to work around them.

It occurs to me that your fields may well contain zero-length strings (""), rather than non-displayable characters.  That's much more likely, now that I think of it. If that's all that's causing the problem, you can write your query expression like this:

    IIf(Len([UnifiedTo] & "") > 0, [UnifiedTo], [PL])

If that is the problem, the query I suggested earlier should have shown it.

Answer 2

Hi,

try with:

Expr1: IIf(IsNull([UnifiedTo]),[PL],[UnifiedTo])

Mimmo

Answer 3

It is text field.... Can i use any other func except null

Answer 4

What type of field is [UnifiedTo] ?  If it is a text field, maybe it contains some nondisplayable character, such as a space or any of a number of other character values that don't display.  In that case, it wouldn't be Null, but you wouldn't see it when it is displayed.

You could try running a query like this, and examining the results to see what might be in a non-Null field that isn't visible:

   SELECT UnifiedTo, , Len(UnifiedTo) As NChars, Asc(UnifiedTo) As CharacterCode 

   FROM <YourTable> WHERE UnifiedTo Is Not Null

If that query returns records in which UnifiedTo appears to be blank, look at he NChars field to see how many characters are in the field, and look at the CharacterCode field to see what ASCII value is first in the field.