MS Access 2007, find and replace character with new line

microsoft-access find-and-replace microsoft-access-2007

16,303

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.

16,303

Muleskinner

Danish Windows (vb.net) and web developer (asp.net, MySQL, javaScript) living in Granada, Spain. Working for danish HRM&D company. Latest work: http://jobmatchprofile.com http://garudabp.com/?l=enu http://www.egaruda.com Latest private project: http://www.wsolver.com/

Updated on September 18, 2022

Comments

Muleskinner over 1 year

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?
- DMA57361 almost 13 years
  
  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?
- Muleskinner almost 13 years
  
  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.
- DMA57361 almost 13 years
  
  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.
- Muleskinner almost 13 years
  
  Not familair with vba (have worked a lot in old vb and vb.net). is it a question of executing a sql query, could you post an example (submit as answer and I will accept it)?
- David W. Fenton almost 13 years
  
  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).
- Muleskinner almost 13 years
  
  @david might be true but has nothing to do with my question / problem
- David W. Fenton almost 13 years
  
  It has everything to do with your question -- if you don't search/replace both, you'll never succeed. The method by which you accomplish it is up to you, but the point is you have to know what you're replacing!
- DMA57361 almost 13 years
  
  @David, the problem was that in MS Access you can't use either of the two line breaking characters in the manner the OP was trying (with the find/replace dialog), hence an alternative method was required. By the look of their comments on my answer, they just replaced it with the string \n instead, and then let the import into a MySQL DB do the rest.
DMA57361 almost 13 years

@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.
Muleskinner almost 13 years

in the end i just ran a replace of ~~ with \n before inserting into my mysql db. Im sure your answer also work, thanks
David W. Fenton almost 13 years

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.
DMA57361 almost 13 years

@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.
David W. Fenton almost 13 years

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 almost 13 years

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.