Concatenation question

N

Narwe

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
 
L

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
 
D

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
 
N

Narwe

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

Thanks for your replies.



Douglas J. Steele said:
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!)



Narwe said:
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
Click to expand...
Click to expand...
 
L

lingpeixue

Narwe said:
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
Click to expand...
 
T

Tonín

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

lingpeixue said:
Narwe said:
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
Click to expand...
them,
so
obviously ConcatenateField: [A] + + [C] etc will not work if there
Click to expand...
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
Click to expand...
Click to expand...
 
Top