Concatenation question

[Guest]

I have 5 fields A, B, C, D, E

I want to create a 6th field that concatenates the values in A through E.
However, sometimes all of these fields are filled, sometimes none of them, so
obviously ConcatenateField: [A] + + [C] etc will not work if there is no
data in one or more of those fields.

Can anyone suggest a solution to this problem?

I should also mention that the fields are filled in order, that is, A first,
then B, then C, etc.

Any help would be greatly appreciated.

Thanks

 

[J. Clay]

Use the NZ function: NZ(a,'') & NZ(b, '') etc.

Jim

 

[Larry Linson]

Using the builtin NZ function will convert Null values to zero, if numeric,
or to an empty string, "", if text/string.

That said, and without enough detail to really be sure, your database
description appears to be un-normalized and perhaps should be re-thought
before you have other problems due to the design. If you'd clarify what you
have and what you are trying to accomplish, there's a good chance that
someone here would have useful suggestions.

Larry Linson
Microsoft Access MVP

 

[Douglas J. Steele]

In addition to what the others have told you, if A through E are text
fields, use & to concatenate, not +.

Null & string or string & Null both evaluation to string, whereas Null +
string and string + Null both evaluate to Null

 

[Guest]

Actually the solution was very simple.
Instead of ConcatenatedFiedl:[A]+, use ConcatenatedField:[A]&" "&.

Thanks for your replies.

In addition to what the others have told you, if A through E are text
fields, use & to concatenate, not +.

Null & string or string & Null both evaluation to string, whereas Null +
string and string + Null both evaluate to Null

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have 5 fields A, B, C, D, E

I want to create a 6th field that concatenates the values in A through E.
However, sometimes all of these fields are filled, sometimes none of them, so
obviously ConcatenateField: [A] + + [C] etc will not work if there is no
data in one or more of those fields.

Can anyone suggest a solution to this problem?

I should also mention that the fields are filled in order, that is, A first,
then B, then C, etc.

Any help would be greatly appreciated.

Thanks

 

[lingpeixue]

I have 5 fields A, B, C, D, E

I want to create a 6th field that concatenates the values in A through E.
However, sometimes all of these fields are filled, sometimes none of them, so
obviously ConcatenateField: [A] + + [C] etc will not work if there is no
data in one or more of those fields.

Can anyone suggest a solution to this problem?

I should also mention that the fields are filled in order, that is, A first,
then B, then C, etc.

Any help would be greatly appreciated.

Thanks

 

[Tonín]

Use the operator "&" instead of the operator "+"
[A]&&.....

I have 5 fields A, B, C, D, E

I want to create a 6th field that concatenates the values in A through E.
However, sometimes all of these fields are filled, sometimes none of

them,

so
obviously ConcatenateField: [A] + + [C] etc will not work if there

is

no
data in one or more of those fields.

Can anyone suggest a solution to this problem?

I should also mention that the fields are filled in order, that is, A first,
then B, then C, etc.

Any help would be greatly appreciated.

Thanks