Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    replace character (A2003)

    I've imported a file and some of the records have the box character. I need to loop through the table and replace this value with a comma. A little research suggests that something like this will do it.

    Me.Fields("followup").Value = Replace(Me!followup, Chr(10), ", ", , , vbTextCompare)

    I'm at a loss, however, about how to set it up. The context I'm used to is form events. Any guidance appreciated.

    E

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: replace character (A2003)

    You should be able to use an update query for this:
    - Create a new query based on the imported table.
    - Add the field or fields that contain box characters.
    - Select Query | Update Query.
    - In the Update To line for each column, enter

    Replace([NameOfField], Chr(10), ", ")

    where NameOfField is the name of that column.
    - Select Query | Run (or click the Run button).
    - Click Yes to confirm that you want to update the records.
    - Switch to datasheet view to check that the box characters have been replaced.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace character (A2003)

    For some reason the empty box character is not changing to a comma. The empty box character is Chr(10) isn't it?

    esw

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: replace character (A2003)

    Not necessarily. Try the following:
    - Select a box character and copy it to the clipboard.
    - Activate the Visual Basic Editor (Alt+F11)
    - Activate the Immediate window (Ctrl+G)
    - Type ? Asc(" then paste (Ctrl+V) and type ") and press Enter.
    - If you're lucky, you'll see a number. Use this number in Chr(...) instead of 10.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace character (A2003)

    I got lucky. It's a pesky 11.

    Thanks!

Posting Permissions

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