Concatenation when some fields are null.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's my problem. (PLEASE HELP!!!!) I have information from many fields,
but no one query record has all the fields populated. Example

Noun Usage Dimension Color Brand Name Material
CATH IV 22g PORTEX
GLOVE PURPLE Nitrile

(I have about 30 different fields, and any one line only has about 1/4
populated)

I want to combine all these fields into one text string. If I use this
syntax:

Description: [Noun]&" "&[Usage]&" "&[Dimension]&" "&[Color]&" "&[Brand_Name]
etc.

I will get a result that has too many empty spaces.
I want my result to be:

GLOVE PURPLE NITRLE

and not

GLOVE PURPLE NITRILE

all those extra spaces add up when there are 30 different fields. I'm
trying to build workable descriptions under 45 characters, and so space is a
limitation.
Is there any way for me to add a space in between the lines if the field is
not null, but not to do so if the field is null? I would be so appreciative
if someone could help me out.
 
Here's my problem. (PLEASE HELP!!!!) I have information from many fields,
but no one query record has all the fields populated. Example

Noun Usage Dimension Color Brand Name Material
CATH IV 22g PORTEX
GLOVE PURPLE Nitrile

(I have about 30 different fields, and any one line only has about 1/4
populated)

I want to combine all these fields into one text string. If I use this
syntax:

Description: [Noun]&" "&[Usage]&" "&[Dimension]&" "&[Color]&" "&[Brand_Name]
etc.

I will get a result that has too many empty spaces.
I want my result to be:

GLOVE PURPLE NITRLE

and not

GLOVE PURPLE NITRILE

all those extra spaces add up when there are 30 different fields. I'm
trying to build workable descriptions under 45 characters, and so space is a
limitation.
Is there any way for me to add a space in between the lines if the field is
not null, but not to do so if the field is null? I would be so appreciative
if someone could help me out.
Click to expand...

Description:[Noun] & (" "+[Usage]) & (" "+[Dimension]) & etc...
 
For the example that you have provided, you can use the following function
in VBA:
NZ()
Trim()
IIF()

See the help file for more specific information.

I have spaced it out so that its a bit more readable. This solution is
untested so please review/test.

HTH

Rob Mastrostefano

Description:
TRIM
(

IIF( Len([Noun]<>0, [Noun], "" ) & IIF( Len([Usage]<>0, " " & [Usage],
"" ) & IIF( Len([Dimension]<>0, " " & [Dimension], "" ) & IIF(
Len([Color]<>0, " " & [Color], "" ) & IIF( Len([Brand_Name]<>0, " " &
[Brand_Name], "" )

)


--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
 
WOW - that's a long way to get there!

I think I'd use the "+" example given to you in the other response.

The "+" is similar to the "&" except that both values must contain
something.

--
Rick B



RobFMS said:
For the example that you have provided, you can use the following function
in VBA:
NZ()
Trim()
IIF()

See the help file for more specific information.

I have spaced it out so that its a bit more readable. This solution is
untested so please review/test.

HTH

Rob Mastrostefano

Description:
TRIM
(

IIF( Len([Noun]<>0, [Noun], "" ) & IIF( Len([Usage]<>0, " " & [Usage],
"" ) & IIF( Len([Dimension]<>0, " " & [Dimension], "" ) & IIF(
Len([Color]<>0, " " & [Color], "" ) & IIF( Len([Brand_Name]<>0, " " &
[Brand_Name], "" )

)


--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com


Matt Lockamy said:
Here's my problem. (PLEASE HELP!!!!) I have information from many
fields,
but no one query record has all the fields populated. Example

Noun Usage Dimension Color Brand Name Material
CATH IV 22g PORTEX
GLOVE PURPLE Nitrile

(I have about 30 different fields, and any one line only has about 1/4
populated)

I want to combine all these fields into one text string. If I use this
syntax:

Description: [Noun]&" "&[Usage]&" "&[Dimension]&" "&[Color]&"
"&[Brand_Name]
etc.

I will get a result that has too many empty spaces.
I want my result to be:

GLOVE PURPLE NITRLE

and not

GLOVE PURPLE NITRILE

all those extra spaces add up when there are 30 different fields. I'm
trying to build workable descriptions under 45 characters, and so space
is a
limitation.
Is there any way for me to add a space in between the lines if the field
is
not null, but not to do so if the field is null? I would be so
appreciative
if someone could help me out.
Click to expand...
Click to expand...
 
Back
Top