How do I set SET CONCAT_NULL_YIELDS_NULL OFF in MS Access?

  • Thread starter Thread starter Guest
  • Start date Start date
Just guessing since you don't really explain what you're wanting.

Try concatenating using the Nz() function around the possible Null values.
 
Hi Wayne

Thanks for your quick reply.

you guessed it right. I am concating two table field and some of the fields
are blank and I am getting blank string as a concat result.

I am going to try the solution you suggested here.

Once again.

Thanks for your help

Regards

Amit
 
To the best of my knowledge, the JET database engine does not have a direct
equivalent of this option. There are, however, various other ways to deal
with possible Null values in JET queries. For example, only the first SELECT
statement in the query below returns a Null value. Note that the NZ()
function is an Access function, so that example will work only when the
query is executed in Microsoft Access. The other methods will work when the
query is executed outside of Microsoft Access.

SELECT Null + TestVal FROM tblTest
WHERE TestID = 24
UNION SELECT Null & TestVal FROM tblTest
WHERE TestID = 25
UNION SELECT NZ(Null, 0) + TestVal FROM tblTest
WHERE TestID = 26
UNION SELECT IIf(Null IS NULL, 0, 1) + TestVal FROM tblTest
WHERE TestID = 27
 
Another thing to consider. If you're concatenating using "+", this is
treated as a mathematical operation. Nulls propagate through equations. If
you concatenate using "&" instead, that alone may solve the problem.

Example:
"This" + Null = Null
"This" & Null = "This"
 
Back
Top