By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,734 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Replace new line character with space

P: 3
I have data in ms access which has new line characters.I want to use the REPLACE function to replace all new lines in that particular column so that the data appears in a single line.What update query should I use?
Mar 21 '10 #1

✓ answered by ADezii

Assuming your Table Name is tblTest, and your Field is named [Field1]:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest SET tblTest.Field1 = Replace([Field1],Chr$(13) & Chr$(10)," ");

Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,638
Assuming your Table Name is tblTest, and your Field is named [Field1]:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest SET tblTest.Field1 = Replace([Field1],Chr$(13) & Chr$(10)," ");
Mar 21 '10 #2

P: 3
I actually tried the above query twice:once with chr(10) and next update query with chr(13) and it worked fine then
Mar 23 '10 #3

NeoPa
Expert Mod 15k+
P: 31,494
If the data were actually New Lines (vbCrLf or vbNewLine) then ADezii's code would work faultlessly. It's common for such data to contain other sequences though, especially if they are embedded within fields. In such an instance it is likely you'll need to replace either or both of Chr(13) and Chr(10). Glad you got it sorted anyway :)

Welcome to Bytes!
Mar 23 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.