REPLACE function on Symbol (Access 03 SP2)

Topic

New Reply

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.

Reply | Quote

Replies

Try

Replace([Description],Chr(10),Chr(13) & Chr(10))

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.

Reply | Quote

So what am I missing? I’m getting an #ERROR returned.

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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?

Reply | Quote

Yes, that’s correct.

Reply | Quote

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?

Reply | Quote

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 ? Asc(Selection) 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.

Reply | Quote

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!

Reply | Quote