Concatenation

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I need to concatenate two fields using + operator as below:-

[Field1] & ("; " + [field2])

It does not work correctly. If field 2 is a null value, it shows
[field1];. Can someone advise me how to remove the semicolon if field 2 is
a null value.

Thanks,

Ray
 
It should behave exactly opposite. The ampersand will show just the
semicolon while the plus sign should force the expression to NULL.

Now, if your [field2] is not really null, but is the empty string, then
you'll have to handle this differently.

[Firld1] & IIF(Nz([Field2},"") = "", "", ": " & [Field2])
 
Ray,

I wouldn't normally bother with the ()s.
[Field1] & "; "+[Field2]
should do the trick. If it's not working, it would indicate that
field2 is not really null. Possibly a "" instead?

- Steve Schapel, Microsoft Access MVP
 
Danny,

Your suggestion works correctly. Thanks!

Ray

Danny J. Lesandrini said:
It should behave exactly opposite. The ampersand will show just the
semicolon while the plus sign should force the expression to NULL.

Now, if your [field2] is not really null, but is the empty string, then
you'll have to handle this differently.

[Firld1] & IIF(Nz([Field2},"") = "", "", ": " & [Field2])
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast


Ray said:
I need to concatenate two fields using + operator as below:-

[Field1] & ("; " + [field2])

It does not work correctly. If field 2 is a null value, it shows
[field1];. Can someone advise me how to remove the semicolon if field 2 is
a null value.

Thanks,

Ray
Click to expand...
Click to expand...
 
Steve,

The modified statement still does not work. Where should I put ""?

Thanks,

Ray

Steve Schapel said:
Ray,

I wouldn't normally bother with the ()s.
[Field1] & "; "+[Field2]
should do the trick. If it's not working, it would indicate that
field2 is not really null. Possibly a "" instead?

- Steve Schapel, Microsoft Access MVP

I need to concatenate two fields using + operator as below:-

[Field1] & ("; " + [field2])

It does not work correctly. If field 2 is a null value, it shows
[field1];. Can someone advise me how to remove the semicolon if field 2 is
a null value.

Thanks,

Ray
Click to expand...
Click to expand...
 
Ray,

Sorry, my meaning was not clear. I meant that the expression...
[Field1] & "; "+[Field2]
will only work as you expect, i.e. to not display the ";" if Field2 is
null. Sometimes a field appears to be empty, but it actually contains
a "" i.e. a zero-length string, which is not null, so the ; will
display. I was offering this as an explanation of why it wasn't
working properly, and the fact that Danny's idea worked for you would
support the theory.

- Steve Schapel, Microsoft Access MVP


Steve,

The modified statement still does not work. Where should I put ""?

Thanks,

Ray

Steve Schapel said:
Ray,

I wouldn't normally bother with the ()s.
[Field1] & "; "+[Field2]
should do the trick. If it's not working, it would indicate that
field2 is not really null. Possibly a "" instead?

- Steve Schapel, Microsoft Access MVP

I need to concatenate two fields using + operator as below:-

[Field1] & ("; " + [field2])

It does not work correctly. If field 2 is a null value, it shows
[field1];. Can someone advise me how to remove the semicolon if field 2 is
a null value.

Thanks,

Ray
Click to expand...
Click to expand...
Click to expand...
 
Back
Top