Memo Field Separation

[AvonLady]

Hello,

I have a table with a memo field. The data in the field is separated by a
comma, like below.

AM42 CHA_StElmo, AM43 CHA_Martinsvill, AM44 CHA_Farina, AM45 CHA_Neoga, AM46
CHA_Sullivan, AM47 CHA_Monitcello, AM48 CHA_Tuscola

Is there a way I can replace the comma and have the data be in a new line,
like below?

AM42 CHA_StElmo
AM43 CHA_Martinsvill
AM44 CHA_Farina
AM45 CHA_Neoga
AM46 CHA_Sullivan
AM47 CHA_Monitcello
AM48 CHA_Tuscola

Thanks.

 

[Clifford Bass]

Hi,

Make a backup first. Then try this, which follows you example of both
a comma and a space:

update tblYourTable
set MemoField = Replace(MemoField, ", ", Chr(13) & Chr(10))
where MemoField like "*, *";

Clifford Bass

 

[John Spencer]

Use the replace function to replace all the commas with new line

Replace([MemoField],", ",Chr(13) & Chr(10))

You need both a carriage return and line feed in Access to get a new line.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[AvonLady]

Thank you both for replying.

Where would I put this code, I've never done this before?

Use the replace function to replace all the commas with new line

Replace([MemoField],", ",Chr(13) & Chr(10))

You need both a carriage return and line feed in Access to get a new line.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I have a table with a memo field. The data in the field is separated by a
comma, like below.

AM42 CHA_StElmo, AM43 CHA_Martinsvill, AM44 CHA_Farina, AM45 CHA_Neoga, AM46
CHA_Sullivan, AM47 CHA_Monitcello, AM48 CHA_Tuscola

Is there a way I can replace the comma and have the data be in a new line,
like below?

AM42 CHA_StElmo
AM43 CHA_Martinsvill
AM44 CHA_Farina
AM45 CHA_Neoga
AM46 CHA_Sullivan
AM47 CHA_Monitcello
AM48 CHA_Tuscola

Thanks.

 

[Clifford Bass]

Hi,

That depends on your goal. My presumption was that you wanted to make
a permanent change to the data in the table. If that is the case, create a
new update query using your table. Select your memo field. In the Criteria
line for that field enter:

like "*, *"

In the UpdateTo line enter:

Replace(MemoField, ", ", Chr(13) & Chr(10))

Then Run the query.

Now, if you do not want to make the change to the data in the table,
but only want to display it in that fashion in a report, you would add an
unbound text box and set its Control Source property to:

=Replace(MemoField, ", ", Chr(13) & Chr(10))

Hope that helps,

Clifford Bass

 

[AvonLady]

Clifford,

THANK YOU, THANK YOU, THANK YOU!

It worked great. I was just using the memo field in a report and making it
pretty.

Thanks again.

 

[Clifford Bass]

Hi,

Good to hear it is working for you. And your are quite welcome!

Clifford Bass