Concatenate text

  • Thread starter Thread starter mdavis via AccessMonster.com
  • Start date Start date
M

mdavis via AccessMonster.com

Here are my fields.

[Figured] This field is sometimes blank.
[Cut]
[Veneer]

I currently have: Expr1: [Figured]& " " & [Cut] & " " & [Veneer]

The problem with this is if the [Figured] field is left blank it leaves a
space in the text string. How do I fix this?

Thank you in advance.
 
Expr1: ([Figured] + " ") & ([Cut] + " ") & [Veneer]

The above takes advantage of the fact that a Null value is propogated by the
+ operator.
 
Here are my fields.

[Figured] This field is sometimes blank.
[Cut]
[Veneer]

I currently have: Expr1: [Figured]& " " & [Cut] & " " & [Veneer]

The problem with this is if the [Figured] field is left blank it leaves a
space in the text string. How do I fix this?

Thank you in advance.
Click to expand...

You can use the fact that both & and + concatenate strings - but +
"propagates nulls", and & treats NULL as an empty string. Try

Expr1: ([Figured] + " ") & [Cut] & " " & [Veneer]

If [Figured] is NULL, the expression in parentheses will evaluate to
NULL and nothing will be concatenated for it.

John W. Vinson[MVP]
 
The solution proposed by John Vinson and Ken Snell are excellent.

An alternative solution for this specific case is to use the Trim function
to eliminate any leading and trailing spaces. This solution does have the
advantage of handling the spaces or zero-length strings in your field. The
proposed solutions have the advantage of handling nulls in the other fields
and eliminates double spaces in the middle of the string.

TRIM ([Figured]& " " & [Cut] & " " & [Veneer])

John Vinson said:
Here are my fields.

[Figured] This field is sometimes blank.
[Cut]
[Veneer]

I currently have: Expr1: [Figured]& " " & [Cut] & " " & [Veneer]

The problem with this is if the [Figured] field is left blank it leaves a
space in the text string. How do I fix this?

Thank you in advance.
Click to expand...

You can use the fact that both & and + concatenate strings - but +
"propagates nulls", and & treats NULL as an empty string. Try

Expr1: ([Figured] + " ") & [Cut] & " " & [Veneer]

If [Figured] is NULL, the expression in parentheses will evaluate to
NULL and nothing will be concatenated for it.

John W. Vinson[MVP]
Click to expand...
 
Back
Top