(RS) Is there a difference between ' ' and null?

[Arsenal]

Hi,

In a report, if I only want to pull in records for which a column, say column A, is populated with blanks, which would be a better expression to use as a detail filter ??

1. [Model].[Temp].[Column A] = ' '
2 [Model].[Temp].[Column A] is null

Thanks

[MFGF]

Hi,

It really depends on what "populated with blanks" means.  If you mean that the column values are missing (ie unpopulated), then the correct test would be "is null".  If you mean that the column has been populated with values of ' ' then the correct test would be = ' '

Regards,

MF.

[Arsenal]

MFGF, thanks for your reply.

Yes, it has been populated with blanks. Using a "is null" doesn't return any rows, but using a '' returns result.

[MFGF]

Hi,

That would be expected behaviour if spaces (or blanks) have been inserted.  Many years ago when I was learning relational theory, it was drummed into me that "null is a state not a value", and null simply means that the value is missing (or unknown).  In your case, you don't have nulls, so it's totally correct for the "is null" test to return nothing.

Regards,

MF.