Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    REPLACE function on Symbol (Access 03 SP2)

    So I have fields that are coming from a linked table that convert the line returns to the symbol: . In the query, I want to write an expression that will eliminate them or replace them with a space. I tried to figure out the REPLACE function and failed of course. I know the character code is a 10, but I just can't get there from here. Help, please.

    Replace («stringexpr», «find», «replace», «start», «count», «compare»)
    for
    Replace([Description],Chr(10),«replace»,«start», «count», «compare»)


    EXAMPLES OF THE DATA I WANT TO CONVERT:
    1200425099wmsgssHello, I am trying to free We need to deliver off this entire position, but G+ will only allow a delivery out to 3 spots. Can you please lift the restriction so I can make the delivery of the full amount. Thanks.
    "1200424479wmsgssHi please re open events 417527 and 417528 they were logically deleted I believe in error. The client is looking for their statements.

    Thank you"
    1200424339wmsgssPlease delete event 307170. 
    thanks
    christine

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

    Re: REPLACE function on Symbol (Access 03 SP2)

    Try
    <code>
    Replace([Description],Chr(10),Chr(13) & Chr(10))
    </code>
    Access uses the carriage return+line feed combination Chr(13) & Chr(10) to break a line. It's not necessary to specify the start, count and compare arguments here, the defaults will work fine.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: REPLACE function on Symbol (Access 03 SP2)

    So what am I missing? I'm getting an #ERROR returned.
    thanks
    christine

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

    Re: REPLACE function on Symbol (Access 03 SP2)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: REPLACE function on Symbol (Access 03 SP2)

    Okay so now I don't receive an #ERROR returned on the query...of course. But, the removal of the  symbol is not working. Hopefully the file comes through.
    Attached Files Attached Files
    thanks
    christine

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

    Re: REPLACE function on Symbol (Access 03 SP2)

    It's bizarre - the text contains ASCII characters 3, 4 and 10. If you want to replace all of them by line breaks, use

    Replace(Replace(Replace([data1],Chr(10),Chr(13) & Chr(10)),Chr(4),Chr(13) & Chr(10)),Chr(3),Chr(13) & Chr(10))

    You may want to use a space or a comma and a space instead of one or more of the Chr(13) & Chr(10).

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: REPLACE function on Symbol (Access 03 SP2)

    I can see where you had to take this. I've just started working with it and it seems to work well. So, i'll be able to change the expression to this: Replace(Replace(Replace([data1],Chr(10)," "),Chr(4)," "),Chr(3)," ") if I choose to use spaces instead of a carriage return, right?
    thanks
    christine

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

    Re: REPLACE function on Symbol (Access 03 SP2)

    Yes, that's correct.

  9. #9
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: REPLACE function on Symbol (Access 03 SP2)

    Okay, rockin! I tried it out both ways and it works wonderfully. I don't suppose you can tell me how you isolated that there were 3 character symbols rather than one in that data could you?
    thanks
    christine

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

    Re: REPLACE function on Symbol (Access 03 SP2)

    I did the following:
    - I selected the first box character I encountered, then pressed Ctrl+C.
    - I pasted it into Word (Ctrl+V) and selected it.
    - I pressed Alt+F11, then Ctrl+G to activate the Immediate window in the Visual Basic Editor.
    - I typed <code>? Asc(Selection)</code> and pressed Enter.
    - I saw 4 so I knew the character was Chr(4).
    - I used Chr(4) in the Replace expression, only to find that there were still box characters in the result.
    - I repeated this to find out that there were also Chr(3) and Chr(10) characters.

  11. #11
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: REPLACE function on Symbol (Access 03 SP2)

    Okay, I get it. I'll use this whenever I return junk to isolate the ASCII code to eliminate using the replace function. Thank you VERY much. This has been great!
    thanks
    christine

Posting Permissions

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