Hi,

I have a report based on one db column to select the data: {Table.ColumnName} <> "value".

At the moment if this field is Null the record is not displayed on the report.

I would like to include the records where this field is Null, either by having a blank field or something like "N/A"

I've tried with selecting the Convert NULL Field Value to Default in Report Options but it does not change anything.

I've also tried in the formula editor: {Table.ColumnName} <> "field" and If IsNull({Table.ColumnName}) = True then ({Table.ColumnName}) ="N/A" but when I do this, my report is just empty. 

Any ideas? thanks in advance for your time!

Have you tried

{TABLE.COLUMNNAME} <> "value" or isnull({Table.ColumnName})

This should work to produce the report if the field is either not the value or if it is null.

Many thanks for your help, your formula really looks like what I'm trying to achieve, unfortunately it doesn't give the results I'm expecting. 

My report is a chart, when I write what you're suggesting it adds more records to the chart, but not the correct number of Null values. It seems to be adding more records to other values...

 This value comes from a pick list in the clients, am I correct in thinking that if the field has been left empty in the clients this is going to be a null value...?

I post the whole formula in case something's missing there...

{TABLE.DATE1} in {?Prompt1?} to {?Prompt2?} and

{TABLE.DATE2} in {?Prompt3?} to {?Prompt4?} and

{TABLE2.COLUMNNAME} <> "Value"

Regards,

Alex

Yes a blank picklist should be stored in the database as NULL.  Your query looks fine though I am thinking maybe you need to bracket the or condition.  Does this work?

 {TABLE.DATE1} in {?Prompt1?} to {?Prompt2?} and

{TABLE.DATE2} in {?Prompt3?} to {?Prompt4?} and

( {TABLE2.COLUMNNAME} <> "Value"  or isnull({Table.ColumnName}) )

(Make sure you add the paranthesis around the last condition.)

Thanks again, when I add the brackets I get the same results as without the last condition of the formula: isnull({Table.ColumnName})

i.e: ( {TABLE2.COLUMNNAME} <> "Value"  or isnull({Table.ColumnName}) )

gives the same results as 

{TABLE2.COLUMNNAME} <> "Value" 

Strange.  Maybe try this approach instead?

{table2.columnname} & "" <> "Value"

This "should" append an empty string to the field, even if the field is null so the comparison should be inclusive of the NULL records.

Hi, unfortunately this doesn't give me the results I'm expecting either!

I get different results depending if I select the 'Convert Null Values to Default' option or not, but I never get the results I'm expecting (I'm comparing with a group in slx) .

I always struggle, even in the client to filter the data with one of the criterias based on a field sometimes empty.

Thanks for your assistance, I think I'm going to give up this approach now, and possibly "hardcode" a text everytime the pick list is left empty. Not an ideal solution, but at least it will eliminate all the possible Null values in the future for this field.