Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Formatting in a field (Access 2000 SR1a)

    To import data from a Word document, I converted all paragraph marks to "~~~". The logic was that the database output would be converted back to a document later and the tildes could be converted back to paragraph marks. Now we are sticking with Access outputs plus the data has been changed so there is no option of revisiting the import.
    In the table or a form, I can manually recreate the paragraphs simply by pressing the enter key. However, with >1,000 records containing tildes I really need to do this through code. Is this possible in Access please?

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

    Re: Formatting in a field (Access 2000 SR1a)

    You can do this with an update query, without using code.

    Create a new query based on the table. Add the field that contains ~~~ to the query grid. Make the query into an update query (Query menu). In the Change to box, enter:
    Replace([FieldName], "~~~", Chr(13) & Chr(10))
    Execute the query by clicking the Run toolbar button (exclamation mark) or selecting Query/Run.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Re: Formatting in a field (Access 2000 SR1a)

    I've tried your suggestion but get an error message "Undefined Function Replace..."
    Is there an obvious reason?
    Also "~~~" is only part of what is in the field so would the update query find that in the string and replace just that rather than replace the whole field?
    Thanks for your help.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    UK
    Posts
    239
    Thanks
    4
    Thanked 1 Time in 1 Post

    Re: Formatting in a field (Access 2000 SR1a)

    Hmmm!
    Couldn't get it to work. Showed colleague how it fell over. He tried it on his PC and it worked. While he was playing with that, I tried various changes to no effect so put it all back as described by you and as originally set up the first time. Tried it again and it worked fine! Don't you just love it when that happens.
    Anyway, many thanks for your help. Mission accomplished.
    Peter

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

    Re: Formatting in a field (Access 2000 SR1a)

    Uhm, I don't have Access 2000 myself, so I can't test it. Replace is new in Access 2000 VBA.

    Unless you have a problem with a missing reference, it would seem that Replace is not supported in expressions. You might create a custom function in a standard module

    Function ReplaceTilde(aValue)
    ReplaceTilde = Replace(aValue, "~~~", vbCrLf)
    End Function

    In the update query, use ReplaceTilde([FieldName]) in the Change To box, where FieldName is the name of the field with the tildes.

    This is the description of the Replace function from VB help:
    <hr>Returns a string in which a specified substring has been replaced with another substring a specified number of times.<hr>
    (if you omit the number of replacements, all occurrences of the specified substring are replaced)

    I hope this works now; if not, somebody with Access 2000 will have to help you.

Posting Permissions

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