UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   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



Posts: 134
Joined: 29-July 08
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: 63,406
Joined: 19-June 07
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,287
Joined: 8-February 00
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



Posts: 134
Joined: 29-July 08
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: 63,406
Joined: 19-June 07
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
Joined: 14-July 05



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: 63,406
Joined: 19-June 07
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,554
Joined: 25-June 05
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
Joined: 14-July 05



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,554
Joined: 25-June 05
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



Posts: 36
Joined: 1-September 04



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,554
Joined: 25-June 05
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



Posts: 36
Joined: 1-September 04



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
 


Custom Search
RSSSearch   Top   Lo-Fi    30th August 2015 - 10:50 AM