Update query to find character and replace with return (or enter)

[sychoangel]

Hello!

I have created an update query that finds characters in a column and replaces them with something else... e.g.

Field: TITLE
Table: DETAILS
Update to: Replace([TITLE],"{","")

That all works ok... but how can I do an update query to replace a character with a carriage return... I have tried
Replace([TITLE],"{",Chr$(10)) (all with & without the $)
Replace([TITLE],"{",Chr$(11))
Replace([TITLE],",{,Chr$(13))

The carriage return (or a shift+enter) needs to display both when the data is displayed on a form & also when printed in a report.

the TITLE field is set to memo....

Any help is greatly appreciated

SYCH

 

[DCrake]

Have you tried Replace([Title],"}",vbCrLf) or vbNewLine

 

[boblarson]

Have you tried Replace([Title],"}",vbCrLf) or vbNewLine

Can't use vbCrLf or vbNewLine in a query (only in code). So, remember you need

Replace([TITLE],"{",Chr(13) & Chr(10))

(you need BOTH the carriage return and line feed, in that order)

 

[sychoangel]

Thanks guys!

Replace([TITLE],"{",Chr(13) & Chr(10)) worked perfectly

I did try the other ones perviously, but they didnt work... thanks for letting me know why.

 

[boblarson]

No problem - glad you have it working.

 

[neideb]

Hi all, i am having a huge difficult trying to replace some characters that appeared in a table I have imported from another program. I need to replace <div>, </div>, <br>, unnecessary white spaces double lines, and others.
I have already a code that spark80 created and it is supposed to do exactly what i need. But when i run the query the text in field stays in the same line. Since there are more than 3000 records and each one has 3 or more lines i believe it will be a big trouble verifying each line and remove strange chr and press enter.
I have another code that replaces character xxx for Chr$(13) & chr$(10). I searched every where and could not find the correspondent chr for <div> and others but no success.

I am reposting here because i feel that this code is similar to what i would like to have.
Replace ([myfield],"<div>", chr(13) & chr(10).
Do you believe i can achieve my goal? Also i do not know how to run a sql query.
I believe i have to convert my query to sqlquery and execute. Tomorow i will check.
I am sure i really need help.
Hope i can count on you all.
Tks a lot