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

bobdydd wrote:
This sub reads a memo field (M) in Table1 and parses it out
Private Sub MemoSplit()
Dim intFor As Integer
Dim ar() As String
Dim r As Recordset
Set r = CurrentDb.OpenRecordset("Table1")

'split lines up
ar = Split(r!M, vbNewLine) 'M is a memo field.

For intFor = LBound(ar) To UBound(ar)
'prints out each line in memo
MsgBox ar(intFor)
Next

r.Close
Set r = Nothing

End Sub

This sub adds a value to memo field M in Table1
Private Sub MemoAdd()
Dim s As String
Dim r As Recordset

Set r = CurrentDb.OpenRecordset("Table1")

s = "This" & vbNewLine & "Is" & vbNewLine & "A" & _
vbNewLine & "6" & vbNewLine & "Line" & vbNewLine & "Memo"

r.AddNew
r!M = s
r.Update

MsgBox "Added " & s

r.Close
Set r = Nothing
End Sub

On Fri, 4 Feb 2011 04:54:47 -0800 (PST), bobdydd
<reallyuseful2004 (AT) yahoo (DOT) co.uk> wrote:

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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Feb 4, 5:02*pm, John W. Vinson <jvinson (AT) STOP_SPAM (DOT) WysardOfInfo.com>
wrote:
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.

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