Search and Replace in Memo Field

  • Thread starter Thread starter Lawrence Cosslett
  • Start date Start date
L

Lawrence Cosslett

I have data in a memo field that contains up to 15 strings of text each
separated with commas. I want to edit within the memo field to replace all
the commas with return characters (either soft or hard line feeds) so that
the text strings appear below each other. I can do this manually but I have
almost 2000 records and not so much free time ! I have tried search&replace
but I cannot get it to work with 'invisible' characters.

Is there an 'Alt' tag that I can put in the [what] box or is there another
workaround that won't take up all my free weekend?

Lawrence
 
Hi Lawrence,

If you're using Access 2002 or later, it's easy to do this with an
update query. Having made a backup copy of your database in case of
accidents, create an update query and set it to update the memo field to
Replace(",", Chr(13) & Chr(10))
This replaces all the commas with linebreaks (carriage return + line
feed).

This also works in some Access 2000 installations (it seems to be a
question of which service packs and so on have been installed). In other
Access 2000 installations and in earlier versions it's more complicated:
post back if you need more.

I have data in a memo field that contains up to 15 strings of text each
separated with commas. I want to edit within the memo field to replace all
the commas with return characters (either soft or hard line feeds) so that
the text strings appear below each other. I can do this manually but I have
almost 2000 records and not so much free time ! I have tried search&replace
but I cannot get it to work with 'invisible' characters.

Is there an 'Alt' tag that I can put in the [what] box or is there another
workaround that won't take up all my free weekend?

Lawrence
Click to expand...
 
Aha !!!

I was meaning to include in my original message that I was using an old
Access97. I am an infrequent database user with only simple needs and have
never found the need to upgrade . . . It seems as if maybe I should.

Any further suggestions are welcome.

Lawrence

====================

John Nurick said:
Hi Lawrence,

If you're using Access 2002 or later, it's easy to do this with an
update query. Having made a backup copy of your database in case of
accidents, create an update query and set it to update the memo field to
Replace(",", Chr(13) & Chr(10))
This replaces all the commas with linebreaks (carriage return + line
feed).

This also works in some Access 2000 installations (it seems to be a
question of which service packs and so on have been installed). In other
Access 2000 installations and in earlier versions it's more complicated:
post back if you need more.

I have data in a memo field that contains up to 15 strings of text each
separated with commas. I want to edit within the memo field to replace all
the commas with return characters (either soft or hard line feeds) so that
the text strings appear below each other. I can do this manually but I have
almost 2000 records and not so much free time ! I have tried search&replace
but I cannot get it to work with 'invisible' characters.

Is there an 'Alt' tag that I can put in the [what] box or is there another
workaround that won't take up all my free weekend?

Lawrence
Click to expand...
Click to expand...
 
You'll need to create a custom VBA function (in a Module), something
like this air code

Public Function CommaToNewLine(V As Variant) As Variant
Dim S As String
Dim j As Long

If IsNull(V) Then
CommaToNewLine = Null
Exit Function
End If

S = CStr(V)
For j = 1 to Len(S)
If Mid(S,j,1) = "," Then
S = Left(S,j-1) & vbCRLF & Mid(S,j+1)
End If
Next
CommaToNewLine = S
End Function



Aha !!!

I was meaning to include in my original message that I was using an old
Access97. I am an infrequent database user with only simple needs and have
never found the need to upgrade . . . It seems as if maybe I should.

Any further suggestions are welcome.

Lawrence

====================

John Nurick said:
Hi Lawrence,

If you're using Access 2002 or later, it's easy to do this with an
update query. Having made a backup copy of your database in case of
accidents, create an update query and set it to update the memo field to
Replace(",", Chr(13) & Chr(10))
This replaces all the commas with linebreaks (carriage return + line
feed).

This also works in some Access 2000 installations (it seems to be a
question of which service packs and so on have been installed). In other
Access 2000 installations and in earlier versions it's more complicated:
post back if you need more.

I have data in a memo field that contains up to 15 strings of text each
separated with commas. I want to edit within the memo field to replace all
the commas with return characters (either soft or hard line feeds) so that
the text strings appear below each other. I can do this manually but I have
almost 2000 records and not so much free time ! I have tried search&replace
but I cannot get it to work with 'invisible' characters.

Is there an 'Alt' tag that I can put in the [what] box or is there another
workaround that won't take up all my free weekend?

Lawrence
Click to expand...
Click to expand...
Click to expand...
 
Back
Top