How do you replace a $ with a line break in a memo field?

[Lee Attlesey]

Hi all

I'm attempting to merge a number of excel columns into one to import into a new MS Access 97 db as a memo field.

I'm copying the colomns into word, converting the table into text seperated by a $ sign, this can then be appended into a memo field.

The part I'm stuck on is then replacing the $ with a line break... can this be done?

Reagrds,
Lee

 

[SydneyGeek]

Re: How do you replace a $ with a line break in a memo field

You'll need to run an Access VBA routine. I wrote this in XP, using DAO so it's compatible with Access 97 and up:

Sub TestReplace()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim MyStr As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Conversion Errors")
With rst
    .MoveFirst
    Do Until .EOF
        .Edit
        MyStr = Replace(![Error Description], "$", Chr(10))
        ![Error Description] = MyStr
        .Update
        .MoveNext
    Loop
End With
Set rst = Nothing
End Sub

Make the following changes to the code so it works in your system:
"Conversion Errors" to "My Table Name"
[Error Description] to [Field containing the text to replace]

Note: Chr(10) is the linefeed character. This routine does do replacements. However, the result is a field with the $ symbols replaced with the "I can't print this" square. No line feeds. Anybody got any ideas?

Denis

 

[mdmilner]

Re: How do you replace a $ with a line break in a memo field

What if you imported the database, then walked the recordset using your own custom replace that inserts vbCrLf?

Hmmm, just tested. Doesn't seem to work. Just ends up truncating the field value at the replacement point.

 

[SydneyGeek]

Re: How do you replace a $ with a line break in a memo field

Looking at mdmilner's response, I realised that Windows doesn't like Chr(10) or vbLf (the standard line feed). This does work:

In the code above, replace Chr(10) with vbCrLf

That gave the result you want, on my test example.
Denis