Null vs Empty Values in Sql

[Guest]

I upsized from MDB data to sql data a few months ago. Fairly smooth
transition. Now I just realized that a Null value and No value are different.
Queries that look for Is Null or Is Not Null are problematic. Is there a
setting I can set in SQL or in Access to treat Null and No values the same?
Why would I not want to? Is there a best practices guide that includes things
like default value or other settings that will keep the data consistent?

Thanks,
Steve

 

[Douglas J. Steele]

No, there's no setting. However, in your query, rather than checking the
value of [Field1], check the value of [Field1] & ""

My usual way to do this, actually, is to check if Len([Field1] & "") = 0.
Surprisingly, that's slightly more efficient, even with the inclusion of the
function call.