How to perform a Find/Replace on non-printing characters in an Access text field

Posted on 2009-02-09
Last Modified: 2013-11-27
Hi there!

Having imported contact details from Outlook into A2003, the problem I have is that for some of the records the address shows on multiple lines, while for others it is all on one line, and there are no spaces between the parts of the address that should be on the different lines. Example shown as attached.

I've checked for what exactly is going on in the field (thanks eghtebas for the code); the multiple line records contain Carriage Returns and Line Feeds (Asc 13 and 10), but the single line fileds have only the Line Feed (Asc 10).

I want to make all the fields look the same, displaying on multiple lines. What is the easiest way to do this? Can I use the Find/Replace facility, and if so how? (I can't get it to find any of these non-printing characters, but I'm not sure of the correct syntax.)

I've been trawiling through the solutions for simialr problems but can't get anywhere, especially as my VB and SQL skills are none too good, so please forgive me. All advice gratefully received.


Question by:BungyStrap
    LVL 92

    Accepted Solution

    Hello BungyStrap,

    UPDATE SomeTable
    SET SomeColumn = Replace(Replace(SomeColumn, Chr(10), Chr(13) & Chr(10)), Chr(13) & Chr(13), Chr(13))



    Author Closing Comment

    Thank you Patrick - I didn't realise that it would be so easy. I didn't use the code itself, but instead used the Replace function in a query.

    Thanks again


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Outlook 2013 Search Issues 3 22
    SQL statement for latest record 15 48
    Printing Problem 13 21
    message box in access 4 23
    Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
    Outlook Free & Paid Tools
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    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…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now