Memo field Instr function

[bobdydd]

Hi

I have a Memo field with customers names and addresses
I would like to write code to make the following happen


MR JOE SAMPLE
1 THE HIGH STREET
BIDCHESTER
SOMERSET
TN8 6TY

MR JOE SAMPLE<br>
1 THE HIGH STREET<br>
BIDCHESTER<br>
SOMERSET<br>
TN8 6TY<br>

I think it is the Instr function but I am getting
nowhere fast

Can anyone help. Thanks

 

[John W. Vinson]

Hi

I have a Memo field with customers names and addresses
I would like to write code to make the following happen


MR JOE SAMPLE
1 THE HIGH STREET
BIDCHESTER
SOMERSET
TN8 6TY

MR JOE SAMPLE<br>
1 THE HIGH STREET<br>
BIDCHESTER<br>
SOMERSET<br>
TN8 6TY<br>

I think it is the Instr function but I am getting
nowhere fast

Can anyone help. Thanks

You don't actually need InStr, just Replace:

UPDATE table
SET memofield = Replace([memofield], Chr(13) & Chr(10), "<br>")

will find all instances of a carriage-return new-line pair and replace them
with the text string <br>. Use

"<br>" & Chr(13) & Chr(10)

if you want to retain both the html tag and the actual new-line pair.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[elg]

Hi

I have a Memo field with customers names and addresses
I would like to write code to make the following happen

MR JOE SAMPLE
1 THE HIGH STREET
BIDCHESTER
SOMERSET
TN8 6TY

MR JOE SAMPLE<br>
1 THE HIGH STREET<br>
BIDCHESTER<br>
SOMERSET<br>
TN8 6TY<br>

I think it is the Instr function but I am getting
nowhere fast

Can anyone help. Thanks

You don't actually need InStr, just Replace:

UPDATE table
SET memofield = Replace([memofield], Chr(13) & Chr(10), "<br>")

will find all instances of a carriage-return new-line pair and replace them
with the text string <br>. Use

"<br>" & Chr(13) & Chr(10)

if you want to retain both the html tag and the actual new-line pair.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Memo fields have been a pain since Clipper87 days. In my opinion they
cause more problems than they solve. I avoid them except in mayge
setup or standing data tables. I never include them into the main
tables which will contain thousands or records.

 

[bobdydd]

             John W. Vinson [MVP]

Thanks John. That works a treat...Many Thanks