Hard Returns in MEMO Field? (All as text)

[BlueWolverine]

Hello,
MS Access 2003 on XP PRO.

I have a ton of text stored in a memo field, thus why it's a memo. I
imported data from excel via linked tables and an append query, but now the
alt-enter hard returns within the excel cells are showing as boxes. squares.
I want the field to store the hard returns for display on a report.

Can this be done or does my data HAVE to be one record per line of text,
which I don't want to do. (Inputs would take a LONG time to get this way.)

Thanks.

 

[Clifford Bass]

Hi,

That is because Excel uses just the line feed character and Access uses
both the carriage return and the line feed. So do this which replaces the
line feed character (UNICODE 10) with both the carriage return character
(UNICODE 13) and the line feed:

UPDATE tblImportedFromExcel SET tblImportedFromExcel.MyMemoField =
Replace([MyMemoField],Chr(10),Chr(13) & Chr(10))
WHERE (((InStr([MyMemoField],Chr(10)))>0) AND
((InStr([MyMemoField],Chr(13)))=0));

The where clause prevents it from changing the same data twice and
therefore causing further problems. So you can run it safely on the same
table if you do multiple imports.

Clifford Bass

 

[BlueWolverine]

Worked perfectly, thank you.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Hi,

That is because Excel uses just the line feed character and Access uses
both the carriage return and the line feed. So do this which replaces the
line feed character (UNICODE 10) with both the carriage return character
(UNICODE 13) and the line feed:

UPDATE tblImportedFromExcel SET tblImportedFromExcel.MyMemoField =
Replace([MyMemoField],Chr(10),Chr(13) & Chr(10))
WHERE (((InStr([MyMemoField],Chr(10)))>0) AND
((InStr([MyMemoField],Chr(13)))=0));

The where clause prevents it from changing the same data twice and
therefore causing further problems. So you can run it safely on the same
table if you do multiple imports.

Clifford Bass

Hello,
MS Access 2003 on XP PRO.

I have a ton of text stored in a memo field, thus why it's a memo. I
imported data from excel via linked tables and an append query, but now the
alt-enter hard returns within the excel cells are showing as boxes. squares.
I want the field to store the hard returns for display on a report.

Can this be done or does my data HAVE to be one record per line of text,
which I don't want to do. (Inputs would take a LONG time to get this way.)

Thanks.

 

[Clifford Bass]

Great! You are welcome.

Clifford Bass