Squares in imported data

Posted on 2006-03-21
Last Modified: 2008-03-03
I've imported data from an Excel spreadsheet. A lot of text fields contain little squares, which I think means there were carriage returns in the original data. I don't want the squares. I think I need an update query to run through the table and either delete them or replace with a space. Any ideas? Thanks.
Question by:Ange
    LVL 92

    Accepted Solution

    Hi Ange,

    Try this:

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

    That will replace the carriage returns and line feeds with spaces, as well as try to clean up any "double spaces"
    there may be.



    Author Comment

    I'm not a programmer  - I know how to put an update query together in design view, but I don't know how to apply your code to my table. Please advise.

    Author Comment

    It just came back to me - query design/SQL view. I've done it, thanks for your help.
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Glad to help, Ange :)

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    746 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

    14 Experts available now in Live!

    Get 1:1 Help Now