Please someone help me with this.

Just guessing since you don't really explain what you're wanting.

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

--
Wayne Morgan
MS Access MVP


"Ameet" <(E-Mail Removed)> wrote in message
news:06355A29-ECDC-411A-84C6-(E-Mail Removed)...
> Please someone help me with this.

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

"Wayne Morgan" wrote:

> Just guessing since you don't really explain what you're wanting.
>
> Try concatenating using the Nz() function around the possible Null values.
>
> --
> Wayne Morgan
> MS Access MVP
>
>
> "Ameet" <(E-Mail Removed)> wrote in message
> news:06355A29-ECDC-411A-84C6-(E-Mail Removed)...
> > Please someone help me with this.
>
>
>

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

--
Brendan Reynolds
Access MVP

"Ameet" <(E-Mail Removed)> wrote in message
news:06355A29-ECDC-411A-84C6-(E-Mail Removed)...
> Please someone help me with this.

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"

--
Wayne Morgan
MS Access MVP


"Ameet" <(E-Mail Removed)> wrote in message
news:073FC9BE-AA5C-4833-ABD1-(E-Mail Removed)...
> 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.

Hi Brendan

Thanks for your update. This definately will be helpful.

Best Regards

Amit

"Brendan Reynolds" wrote:

>
> 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
>
> --
> Brendan Reynolds
> Access MVP
>
> "Ameet" <(E-Mail Removed)> wrote in message
> news:06355A29-ECDC-411A-84C6-(E-Mail Removed)...
> > Please someone help me with this.
>
>
>