concatenate

  • Thread starter Thread starter JohnLute
  • Start date Start date
J

JohnLute

Materials: Concatenate("SELECT Material
FROM tblPKPKMSsMaterials
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
Material")

How can I squeeze [MaterialComments] into this? For example, a value for
[Material] might be "Other (Specify)" which results in a value in
[MaterialComments]. Also, any other [Material] may have a [MaterialComments]
therefore I need to concatenate[MaterialComments] into this so that it
returns the following:
Material | MaterialComments
LLDPE |
Paper | Groundwood
Other (Specify) | Misc. Recycled

as:
LLDPE, Paper: Groundwood, Other (Specify): Misc. Recycled

I figured throwing in the ":" would help clarify things.

Is this possible? I can't see how to fiddle this in.

Your help is greatly appreciated. Thanks!
 
To concatenate many child records into one field.
--
www.Marzetti.com


Rui said:
Why are you using concatenate?



JohnLute said:
Materials: Concatenate("SELECT Material
FROM tblPKPKMSsMaterials
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
Material")

How can I squeeze [MaterialComments] into this? For example, a value for
[Material] might be "Other (Specify)" which results in a value in
[MaterialComments]. Also, any other [Material] may have a [MaterialComments]
therefore I need to concatenate[MaterialComments] into this so that it
returns the following:
Material | MaterialComments
LLDPE |
Paper | Groundwood
Other (Specify) | Misc. Recycled

as:
LLDPE, Paper: Groundwood, Other (Specify): Misc. Recycled

I figured throwing in the ":" would help clarify things.

Is this possible? I can't see how to fiddle this in.

Your help is greatly appreciated. Thanks!
Click to expand...
Click to expand...
 
Do you want this is separate fields or do you want to include the data in the
same field?

If the former, you will need two separate concatenate calls, one for each field.

If the latter try
Materials: Concatenate("SELECT Material &
IIF(MaterialComments is Not Null, "" : "" & MaterialComments,"""") as Result
FROM tblPKPKMSsMaterials
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
Material")


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Dang!

You got that in what - 2 minutes? I was pecing at that for 2 minutes x more
than I want to admit!

Thanks, John!

--
www.Marzetti.com


John Spencer said:
Do you want this is separate fields or do you want to include the data in the
same field?

If the former, you will need two separate concatenate calls, one for each field.

If the latter try
Materials: Concatenate("SELECT Material &
IIF(MaterialComments is Not Null, "" : "" & MaterialComments,"""") as Result
FROM tblPKPKMSsMaterials
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
Material")


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Materials: Concatenate("SELECT Material
FROM tblPKPKMSsMaterials
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
Material")

How can I squeeze [MaterialComments] into this? For example, a value for
[Material] might be "Other (Specify)" which results in a value in
[MaterialComments]. Also, any other [Material] may have a [MaterialComments]
therefore I need to concatenate[MaterialComments] into this so that it
returns the following:
Material | MaterialComments
LLDPE |
Paper | Groundwood
Other (Specify) | Misc. Recycled

as:
LLDPE, Paper: Groundwood, Other (Specify): Misc. Recycled

I figured throwing in the ":" would help clarify things.

Is this possible? I can't see how to fiddle this in.

Your help is greatly appreciated. Thanks!
Click to expand...
Click to expand...
 
Back
Top