Memo field Instr function

B

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
 
S

Salad

bobdydd said:
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

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
 
J

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
 
E

elg

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

bobdydd

             John W. Vinson [MVP]

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top