Solved

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

Posted on 2012-03-30
3
375 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
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

730 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