Sometimes doesn't recognize Null values, but does others.

[#1]

I have two fields that contain a mixture of data and null values each, and what I am trying to do is replace the Nulls in each field with 'No  Fieldname.' and then join both the fields together with an ' - ' in the middle.

I decided to do this using the following formula:-

=If(IsNull(Field1), 'No Field1', Field1) & ' - ' & if(IsNull(Field2), 'No Field2', Field2)

but looking at the field afterwords, i just get a blank space where any of the Null positions were from Field1 or 2.

More confusingly, If do either of the tests on their own, they work, IE

=If(IsNull(Field1), 'No Field1', Field1)

produces the correct results for field1 with nulls as 'No Field 1'

=if(IsNull(Field2), 'No Field2', Field2)

produces the correct results for field2 with nulls as 'No Field 2'

its only when I try to do them together suddenly it returns nothing for the nulls.

I'm really scratching my head about this one, any help anyone would offer would be greatly appreciated.

Many thanks,

Adam Hayden

[Clever_Anjos]

Try

=alt(Field1, 'No Field1') & ' - ' & alt(Field2, 'No Field2')

[daniel_kusiak]

Hi Adam Hayden,

In script editor try:

If(IsNull(Field1), 'No Field1', Field1) &'-'& If(IsNull(Field2), 'No Field1', Field2) as Field3

[#4]

Thank you both for your replies,

I first tried using "=alt(Field1, 'No Field1') & ' - ' & alt(Field2, 'No Field2')" but this resulted in every cell from Field1 and 2 that had data, displaying as "'No field1' - 'No field2'" and any field from field 1 or 2 that was blank, resulted in the formula returning a blank as well.

I then tried adding If(IsNull(Field1), 'No Field1', Field1) &'-'& If(IsNull(Field2), 'No Field1', Field2) as Field3 to the script editor. This produced the correct result when Field 1 and 2 contained data, but when they were empty the resultant Field 3 was also empty.

Its almost as if the program isn't seeing the fields as Null and is copying the 'Blank'. but paradoxically, if i make a field 4 with =If(IsNull(Field1), 'No Field1', Field1) for a row where field 1 is blank this field returns 'No Field1'!

Any further suggestions are much appreciated.

Many thanks,

Adam Hayden

[jonathandienst]

IsNull() will detect null values, and I am not sure whether you have null values or blank values -  blank values are not null, so IsNull will return false. To detect blanks and nulls:

     =If(Len(Field1) = 0, 'No Field1', Field1) & ' - ' & if(Len(Field2) = 0, 'No Field2', Field2)

A blank string and null both have a length of 0.

[dusasuman]

Hi Adam Hayden,

This code may resolves the issue...

If(Len(Trim(Field1))>0, Field1,'No Field') &'-'& If(Len(Trim(Field2))>0, Field2,'No Field') as Feld3

If not try to add the logic of ISNULL() to above logic. The combination of Finding the length for having empty values and null values.

--Suman

[#7]

Thank you again for more suggestions,

I tried using If(IsNull(Field1) or Len(Trim(Field1))<1, 'No Field1', Field1) &'-'& If(IsNull(Field2)  or Len(Trim(Field2))<1, 'No Field2', Field2) as FIeld3

This once again displays data correctly for all the times Field1 and 2 have data, but all the times either is blank it is just returning a blank in field 3.

Its strange that if I use =If(IsNull(Field1), 'No Field1', Field1) it correctly recognizes Nulls in Field 1, but when I ask it to do that with two fields, it suddenly stops seeing either.

your continued help is much appreciated.

Many thanks

Adam Hayden

[dusasuman]

that was really strange. The has to show the values with

Len(Trim(Field1))<1, 'No Field1', Field1) &'-'& If(IsNull(Field2)  or Len(Trim(Field2))<1, 'No Field2', Field2) as FIeld3 .

Not sure how its happening

Provide sample data code if possible

--Suman

[Digvijay_Singh]

The length check is considered best option in such situations as mentioned by jontydkpi

See the attached sample how you are trying differently..

[sasiparupudi1]

if(Len(Field1)>0,Field1,

    if(Len(Field2)>0,Field2,

       if(Len(Field1) = 0 and Len(Field2) = 0, 'No Field1 - No Field2')))