MS Access 2007, find and replace character with new line

Question

In Access 2007 how do I replace a character (say "~") with a new line. I have tried using the Replace box with ALT+010 but it is not accepted as new line.

Any suggetsions?

Answer 1

The alternative to a VBA solution, is a fairly simple SQL query.
^{(I realise I posted the VBA solution, but for some reason I only thought about this one second, sorry).}

Without being able to test it on your database I can't guarantee safety, so please backup before trying, and you'll need to replace the table and field names as required.

UPDATE Table1 
SET data = Replace([data],"~~",Chr(13) + Chr(10));

You should, in fact, be able to create and run this using the query builder if you wish, just make sure you're working on an update query and enter the Replace() function from above as the update to value for the relevant column.

Answer 2

Here's a quick VBA function which should do what you want. Drop it in to a new code module within the database, tweak it as necessary, and then give it run.

Obviously, I can't guarantee this is entirely safe without testing it on your database, so make a really good backup before trying this out! And, I've not made any effort to generalise the function, just tailor it to the requirements given.

Note that I'm using this on a test database that just has Table1 with the fields ID and data, ID being the PK and data being the memo field we wish to modify. You'll have to make adjustments to the code below to account for your local table and field names (I've commented these lines for you).

Function SearchReplace()
    Dim db As DAO.Database, rs As DAO.Recordset, sSQL As String, sData As String

    'Get the record set we wish to modify
    Set db = CurrentDb
    sSQL = "SELECT * FROM Table1"                     '[replace table name]
    Set rs = db.OpenRecordset(sSQL)

    'iterate through each record
    While Not rs.EOF

        'check for the matching sub string in the field "data"
        sData = rs![Data]                             '[replace target field]
        If InStr(1, sData, "~~") Then

            'modify the data string, replacing "~~" with a line break
            sData = Replace(sData, "~~", vbCrLf)

            'escape any ' characters to avoid errors and/or injection
            sData = Replace(sData, "'", "''")

            'update the table with the modified string'
            sSQL = "UPDATE Table1" & _                '[replace table name]
                   " SET data='" & sData & "'" & _     '[replace target field]
                   " WHERE [ID] = " & rs![ID]          '[replace PK field]
            db.Execute sSQL

        End If

        rs.MoveNext
    Wend

    'destroy record set to be tidy
    rs.Close
    Set rs = Nothing

End Function