Solved

Replace ", " with Return and Line Feed - Replace Function Microsoft Access

Posted on 2012-03-30
3
376 Views
Last Modified: 2012-03-30
Hi all,

I am importing a list of addresses into an Access table and a typical address is:
100 High Street, Some Town, Some County, Some Postcode

I need to use the replace function to get it to look like:
100 High Street
Some Town
Some County
Some Postcode

Am happy to replace function at query level to "a" with "b", but not sure how to handle the above regarding Returns and Linefeeds, Thanks
0
Comment
Question by:Paul-bbc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37786291
Use this In an update query:

UPDATE YourTable
SET YourField = Replace(YourField, ",", Chr(10) & Chr(13))

Make a backup before testing any action query.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37786295
Sorry - I've got that backwards.  It should be:

UPDATE YourTable
SET YourField = Replace(YourField, ",", Chr(13) & Chr(10))



Chr(13) & Chr(10)  is Carriage Return/Line Feed (the equivalent of vbCRLF in VBA programming)

You might also try this - which may avoid extraneous spaces in your data after breaking the lines apart (it replaces 'comma space' with CR/LF):

UPDATE YourTable
SET YourField = Replace(YourField, ", ", Chr(13) & Chr(10))
0
 

Author Closing Comment

by:Paul-bbc
ID: 37786319
Thanks, worked fine, just needed to amend to ", " i.e. include the space character to be replaced as well.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question