Search and Replace in Memo Field

[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

 

[John Nurick]

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

 

[Lawrence Cosslett]

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

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

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

 

[John Nurick]

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

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

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