Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
I think we need a little more information to be able to help you effectively. Where are you trying to replace the text? (In a field in a table? In a text box in a form? etc) Do you need to do this change as a one-off, every time you add a record and/or to every record already in the database? – DMA57361 Jun 27 '11 at 8:32
I think the question is clearly formulated, but to be more specific: In MS Access 2007 I have one column of the type MEMO. This column contain several rows. In the text of some cells I have the two characters "~~". I need to find and replace all occourence of "~~" with a New Line. I tried to use the Find and Replace dialog, with ALT+010 in the Replace With text field (as I found this suggested somewhere else) but this is not working. – Muleskinner Jun 27 '11 at 8:39
Are you adverse to a VBA or macro solution? I can see a simple procedure that simply steps through all the rows in the table and performs a search and replace being the most effective solution. – DMA57361 Jun 27 '11 at 8:43
Not familair with vba (have worked a lot in old vb and is it a question of executing a sql query, could you post an example (submit as answer and I will accept it)? – Muleskinner Jun 27 '11 at 8:50
The key point is that in Windows, a carriage return has two parts, the carriage return and the line feed. This in contrast to Linux and Mac, both of which have only one of the two. Thus, you have to use TWO characters, Asc(10) and Asc(13). – David W. Fenton Jun 28 '11 at 21:09
up vote 1 down vote accepted

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.

share|improve this answer
in the end i just ran a replace of ~~ with \n before inserting into my mysql db. Im sure your answer also work, thanks – Muleskinner Jun 28 '11 at 12:38
This will only work within Access, as the Replace() function is not available via ODBC or OLEDB. It can actually be done with Len(), Mid() and Left() (which are all available from outside Access), but it's more convoluted. @onedaywhen wrote up that version on StackOverflow a while back when I pointed it out, but I can't find it now. – David W. Fenton Jul 1 '11 at 0:31
@David well yes, but the OP wanted it to work within Access, and there's no point going with a convoluted SQL solution for what sounds like something that will be a one-off event when a simple function is available in the given context - in fact that's why I suggested a VBA solution first, to avoid a convoluted SQL one, it was only after doing so that it dawned on me that Access can use the Replace() function in SQL and produce such a simple solution. – DMA57361 Jul 1 '11 at 7:19
How do you know for a fact from the original question that the context is to run the code within Access 2007? It is a common flaw of StackOverflow that the Access tags are used universally to cover both question about actually working within Access as well as the much larger body of questions about using Jet/ACE data files without Access at all. The question is actually ambiguous on that point, even if the comments from the original questioner seem to have clarified that, yes, he is working in Access 2007. – David W. Fenton Jul 1 '11 at 20:57
My comment is not disputing that your answer is a valid answer -- it's intended as informational to make it clear to others who might try it from outside Access and not know why it doesn't work. – David W. Fenton Jul 1 '11 at 20:57

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


    'destroy record set to be tidy
    Set rs = Nothing

End Function
share|improve this answer
thanks a lot - will try it out asap – Muleskinner Jun 27 '11 at 9:26
@Mule, please check out my second (SQL based) answer first, it's probably much much easier to use and potentially less prone to error - I was just thinking about the problem in the wrong direction it seems. – DMA57361 Jun 27 '11 at 9:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .