UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Replace all chr(10) to chr(13) in memo fields    
 
   
Pablito
post Nov 27 2008, 11:06 PM
Post #1

UtterAccess Addict
Posts: 134
From: Singapore



Excel's Alt+Enter generates symbol char(10).
When we copy-paste it to Access we see squares.
Access Ctrl+Enter generates chr(13).
How to write code to replace all symbols chr(10) to chr(13)?
Go to the top of the page
 
+
theDBguy
post Nov 27 2008, 11:09 PM
Post #2

Access Wiki and Forums Moderator
Posts: 58,549
From: SoCal, USA



Not sure if this will work, but you can try:
FieldName]=Replace([FieldName], Chr(10), Chr(13))
Hope that helps...
Go to the top of the page
 
+
R. Hicks
post Nov 27 2008, 11:12 PM
Post #3

UA Forum Administrator
Posts: 40,051
From: Birmingham, Alabama USA



Have a look at the Replace() function in Access VBA help files ...

RDH
Go to the top of the page
 
+
Pablito
post Nov 27 2008, 11:59 PM
Post #4

UtterAccess Addict
Posts: 134
From: Singapore



OKm Replace works, it chanhes chr(10) to chr(13) and vice versa.
But... both these symbols are squares in table and form control...
How to make breaks instead of squares? That is the question.
Go to the top of the page
 
+
theDBguy
post Nov 28 2008, 12:00 AM
Post #5

Access Wiki and Forums Moderator
Posts: 58,549
From: SoCal, USA



Sorry about that... try:
eplace([YourField], Chr(10), Chr(10) & Chr(13))
Hope that helps...
Go to the top of the page
 
+
vtd
post Nov 28 2008, 01:13 AM
Post #6

Retired Moderator
Posts: 19,667



Hi theDBguy,
think you need:
Chr(13) & Chr(10)
and not
Chr(10) & Chr(13)
Go to the top of the page
 
+
theDBguy
post Nov 28 2008, 10:35 AM
Post #7

Access Wiki and Forums Moderator
Posts: 58,549
From: SoCal, USA



Thanks, Van. I think you're right. thumbup.gif
Go to the top of the page
 
+
RuralGuy
post Nov 28 2008, 10:44 AM
Post #8

UtterAccess VIP
Posts: 2,538
From: @ 8300' in the Colorado Rocky Mountains



You might also need to preceed the vbCrLf sequence with a space Chr(32).
Go to the top of the page
 
+
vtd
post Nov 28 2008, 03:42 PM
Post #9

Retired Moderator
Posts: 19,667



Hi Allan
I am fairly sure vbCrLf / Chr(13) & Chr(10) works fine without the preceding space character...
Go to the top of the page
 
+
RuralGuy
post Nov 28 2008, 06:28 PM
Post #10

UtterAccess VIP
Posts: 2,538
From: @ 8300' in the Colorado Rocky Mountains



Hi Van,
I just remember having a problem once and it took prepending a space to the sequence before the little squares went away and the sequence started working as I would expect. Just tossed in just in case.
Go to the top of the page
 
+
tcarroll
post Mar 9 2009, 12:18 PM
Post #11

UtterAccess Member
Posts: 36



I am having this same problem but I am a novice with Access and did not understand the solution. I use Access to import data from an Excel spreadsheet and the memo fields all containg these squares in place of carriage returns. I have read enough to fully understand what is happening but the solutions are over my head. Can someone walk me through relpacing the chr(10) to chr(10) & chr(13) so that when generate a report the memo fields will be formated with carriage returns? Thanks.
Go to the top of the page
 
+
RuralGuy
post Mar 9 2009, 05:07 PM
Post #12

UtterAccess VIP
Posts: 2,538
From: @ 8300' in the Colorado Rocky Mountains



Copy the following line:
UPDATE YourTable SET YourTable.YourMemoField = Replace(YourMemoField,Chr(10),Chr(13) & Chr(10));
...and paste it into the SQL view of a new query; using YourTable and YourMemoField names of course.
Go to the top of the page
 
+
tcarroll
post Mar 10 2009, 08:34 AM
Post #13

UtterAccess Member
Posts: 36



Thank you very much. I really appreciate it. One more problem. The report looks great in Access but when I export to a Rich Text I get a bunch of carriage returns in the memo fileds that did not exist in the original Excel or Access fields. Have you ever seen this problem?
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 02:37 AM