Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Appending data into memo field

    In Access 2000, I am using an append query to append data that was imported from another database into a memo field. After the append is complete the data in the memo field contains strange characters represented as square boxes. I can't find a way to simply remove the characters. If I move to the memo field and simply press F2 to edit the field and press enter, the boxes disappear if they are at the end of the text. However in some cases they are in the middle of the paragraph and you have to manually select and delete them. Does anyone have an idea of how I could remove these characters without going through each record and removing them manually?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Appending data into memo field

    Sorry it has taken so long to respond. Not usual.

    You may be able to use replace. I would open the table and then select and copy one of the squares. Then use Edit/Replace and put the square in the "find" box (using ctrl-v) put nothing in the "replace" box, select your memo field in the "Look In" box and finally "Any part of field" in the Match box.

    You may need to do this several times. The square represents any unknown character and there may be several types of character in your source data. Most commonly these would be formatting characters like characters to denote bold or italic text etc. The first time you might pick up the square representing Start Italic, so you will eliminate all start italic symbols in all the memo fields, next time it might be End Italic, or something else. After about 6-10 iterations you will have cleared almost all of the offending characters. Without reading every memo field you cannot be certain you have found them all.
    David Grugeon
    Brisbane Australia

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending data into memo field

    When I select one of these character boxes and paste into the Find What box using the Edit, Replace feature, nothing appears and the Find Next, Replace,and Replace All buttons are greyed out as if nothing was yet pasted. So Access is not recognizing these characters at all.

    When I first arrow down to this field in my table the highlighted area extends way beyond the end of the text and encompasses these little boxes, however; if I press Shift+F2 and then press enter, the text only is highlighted and the strange boxes disappear. My thought was if I could create code to do the equivalent of Shift+F2 and then Enter to each record maybe this would keep me from having to do this manually since there are around 3000 records but I'm not sure where to start.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Appending data into memo field

    Hi Judy,

    I'm not sure if this is the case with your sitaution or not, but I've noticed that Linefeed and CharacterReturns are often displayed as little black squares in certain circumstances. Maybe that's all it is. When you use the Zoom box (which probably does display line breaks) they're not there, but in the single-line box they are...

    Just a longshot... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Appending data into memo field

    HI again Judy

    OK. Two possible approaches, one is to look at the source data and identify what exactly these boxes represent, then you could search for that character using CHR(). you would probably have to write code to do this.

    Another approach would be to edit each field. This will only work if a simple edit works. Try going into a field (not using the zoom box) and add a space, then delete it, then exit the field. Has the field been cleaned? If so you need code to edit each of the memo fields.

    If the edit does not work the sledgehammer approach might be to step through each field, then through each character in that field, test its value using asc() against the ranges of visible characters in the ASCII table and delete those outside this range. You will need to allow for linefeed (10) Tab (9) return (13) and other formatting characters. In VB Help look for "Character set 0-127"

    Sorry this is not a complete answer but it might point you in the right direction.
    David Grugeon
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •