Please help! I need to remove characters from a memo field, and enter new lines

[Urbane]

Hello,



I'm not sure if this is best place to ask this question but I figured it will probably be an update query.

I have a database (Access 2003) with a memo field that is concanated from multiple other fields. The table is called "register" and the field is called "Response_to_Submission". The field has text which is separated by these characters (without quotes) " ** ".

I want to get rid of the " ** " and insert two new lines in-between each line of text. The problem is, each record has a different arrangement of " ** ". Sometimes it will start with a line of text that I want to keep, and be followed by any number of sets of " ** " before getting to a new line of text. Other times it will start with sets of " ** " before getting to the first line of text that I want to keep.

I am not permitted to change the way the field is populated and the field will be subject to frequent updating. Therefore I need an additional query that can get rid of the unwanted characters and insert the new lines, which I can run everytime the field is updated.

Here are three examples of what the data in the field looks like:

" ** ** ** ** LuBF5 Test response ** LuD1 Test response ** ** ** ** ** ** ** ** ** ** ** ** EWi1 Test response ** ** EV1 Test response ** ** EG1 Test response ** ** TT2 Test response ** TT3 Test response ** ** TT5 Test response ** ** ** TWC1 Test response ** ** ** IP1 Test response ** ** IS1 Test response ** ** ** IC1 Test response ** ** ** IC4 Test Response ** ** ** IC7 Test response ** ** ** OPv1 Test response ** ** OF1 Test response ** ** ** ** ** ** ** ** ** ** ** OSEQ1 Test response **"


"LUBF1 Test response ** LuBF2 Test response ** ** LuBF4 Test response ** ** LuD1 Test response ** ** ** LuA2 Test response ** ** ** ** ** ** LuP5 Test response ** ** ** EWi1 Test response ** ** EV1 Test response ** ** EG1 Test response ** TT1 Test response ** TT2 Test response ** TT3 Test response ** ** TT5 Test response ** ** TT7 Test response ** TWC1 Test response ** ** TPt2 Test response ** IP1 Test response ** ** IS1 Test response ** ** ** IC1 Test response ** ** ** IC4 Test Response ** ** ** IC7 Test response ** ** OC2 Test response ** OPv1 Test response ** ** OF1 Test response ** ** ** ** ** OCon1 Test response ** OCon2 Test response ** OCon3 Test response ** ** ** ** OSEQ1 Test response ** OEmp1 Test response"

" ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** TT2 Test response ** ** ** ** ** ** ** TPt1 Test response ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** **"

I want to remove the " ** ", keep the test responses, and have two new lines in-between each test response present. The test responses are currently just fillers. Once the system is active, they will be replaced with paragraphs of text.

There are thousands of records that I need to update. I've been pulling my hair out trying to get this to work and I just can't figure it out. Can anyone help me solve this issue? I will be very grateful for any assistance.

Thank you!

[June7]

Google: Access update query replace asterisk wildcard

Does this help
http://www.utteraccess.com/forum/Upd...-t1955260.html

[Urbane]

Thanks June,

Useful, but that doesn't quite get what I need. It certainly helps to remove or replace certain characters in the field. So using that, I could replace the " ** " with carraige returns. But then I'd have many more carriage returns than what I want. I need to have no carriage returns at the beginning, two carriage returns between everyline of text, and no carriage returns at the end. If I could combine that, with something to solve the carriage return issue, the job would be done

Anyone have any ideas?

Thanks!

[June7]

Let x represent the memo field.

Since I can't get Replace to work with a wildcarded string, try:
Replace(Replace(Replace(Replace(x," **", "."),". ",Chr(13) & Chr(10) & Chr(13) & Chr(10)),".",""),"*","")

This could result in a string with CrLf at begin/end but not sure how to fix that.

[Urbane]

Hi June and thanks for that,

I could be doing something wrong, but running an update query with that in the update section replaced all the data in the "Response_to_Submission" field, with the text "Response_to_Submission" for every record.

If it helps, I can see if I can get permission to change the way the field is populated so that it no longer uses asterixes.

[June7]

Don't know why that would happen. Would have to see the query sql statement.

If you can, it's always nice for data to import clean. However, you need some character in those positions to perform the Replace on to get the CrLfs inserted. It's just the multitude of asterisks and spaces that really complicates.

[Urbane]

I've managed to get the way the field updates changes so that there are no more asterixes. The data in the field now looks like this:

" ZZ ZZ ZZ ZZ ZZ LuD1 Test response ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ TT1 Test response ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ TPt1 Test response ZZ ** ZZ ZZ ZZ ZZ IWc1 Test response ZZ ZZ ZZ ZZ IC4 Test Response ZZ ZZ ZZ ZZ ZZ OC2 Test response ZZ ZZ ZZ ZZ ZZ OPa2 Test response ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ ZZ"

I retro-fit the query you provided using this SQL (needed to add square brackets around the field name):

UPDATE Register SET Register.Response_to_Submission = Replace(Replace(Replace(Replace([response_to_submission]"," ZZ","."),". ",Chr(13) & Chr(10) & Chr(13) & Chr(10)),".",""),"Z","");

And it worked!! Thanks for that!

However, there's still one more step to go... removing all the extra lines at the beginning of the field and inbetween text. The data in the field now looks like this:

"




LuD1 Test response
















TT1 Test response






















OPv1 Test response











OG1 Test response

"


How can I get rid of all the extra lines so that the field starts with the first line of text, and then has two new lines inbetween each line of text?

Thanks again!

[June7]

In my test of the expression, I don't get all those extra CrLfs, with either ** or ZZ.


Your SQL shows an extra quote mark. Don't know how the this could run at all:

UPDATE Register SET Register.Response_to_Submission = Replace(Replace(Replace(Replace([response_to_submission]"," ZZ","."),". ",Chr(13) & Chr(10) & Chr(13) & Chr(10)),".",""),"Z","");

However, you might not want the outermost Replace because that will replace all z's or Z's in the text. Probably was better with the asterisks.


Why is the data created with repeating characters anyway? I had thought you meant the repetition would be eliminated. One asterisk seems sufficient. I just don't know how to eliminate the CrLf that might result at begin and end of string. If they could be counted on to always result, that can be handled, it's the uncertainty of their presence that complicates. By the data samples you provide, that is not a consistent pattern.

[Urbane]

Sorry the extra " was the result of my copy/paste fail.

I just got the same results using this:

UPDATE Register SET Register.Response_to_Submission = Replace([response_to_submission]," ZZ ",Chr(13) & Chr(10));

But this still give me all the extra lines. I'm not sure how your query is removing the lines? Can you explain it to me? I'm not very advanced at Access.

Thanks

[June7]

Don't know what to say. I created a table with memo field and pasted your sample data into it - without the quote marks. This is the UPDATE sql:

UPDATE Table1 SET Table1.test = Replace(Replace(Replace(Replace([test]," **","."),". ",Chr(13) & Chr(10) & Chr(13) & Chr(10)),".",""),"*","");

The result is like:

LuBF5 Test response

LuD1 Test response

EWi1 Test response

EV1 Test response

...

[Urbane]

Wierd. For me it replaces all the ** with carriage returns... so where there are five ** in a row, I get five carraige returns instead.

[June7]

I am using Access 2007/2010. Could be 2003 has an issue with the expression? More likely there is something about your data that doesn't carry over into the posted examples. If you want to provide file I will test. Follow instructions at bottom of my post.

[Urbane]

SOLVED!

VBA came to the rescue on this one. For the record, I've included the code below. Big thanks to CJ London and John Big Booty on the access programmers forums! Also, thank you June for all your help on this, much appreciated!

Function Clean_Up()
Dim Mdb As Database
Dim Rst As DAO.Recordset
 
Set Mdb = CurrentDb
Set Rst = Mdb.OpenRecordset("SELECT * FROM Register")
While Not Rst.EOF
 
    'step 1: replace text set with a pipe marker (also note the extra space in the text set) should leave you with LUBF1 Test response ||||LUBF2 Test response
    Rst.Edit
    Rst.Fields("Response_to_Submission") = Replace(Rst.Fields("Response_to_Submission"), " ** ", "|")
    
    'Step 2: replace 2 pipes with 1 until only one remains as a marker should leave you with LUBF1 Test response |LUBF2 Test response
    While InStr(Rst.Fields("Response_to_Submission"), "||") ' 2 pipes
        Rst.Fields("Response_to_Submission") = Replace(Rst.Fields("Response_to_Submission"), "||", "|")
    Wend
 
    'Step 3: replace space and single pipe with linefeeds - but not one right at the beginning if it exists
    Rst.Fields("Response_to_Submission") = Replace(Rst.Fields("Response_to_Submission"), "|", vbCrLf & vbCrLf & vbCrLf, 2)
 
    'Step 4: replace space and single pipe at the beginning if it exists
    Rst.Fields("Response_to_Submission") = Replace(Rst.Fields("Response_to_Submission"), "|", "", , 1)
 
    Rst.Update
    Rst.MoveNext
 
Wend
MsgBox "Finished!"
 
Set Rst = Nothing
Set Mdb = Nothing
End Function