Concatenation

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
 
D

Danny J. Lesandrini

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])
 
S

Steve Schapel

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
 
R

Ray

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
 
R

Ray

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
 
S

Steve Schapel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top