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

[Urbane]

RESOLVED - I need to remove characters from a memo field, and enter new lines

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!
​

 

[John Big Booty]

Have a look at the Replace() function. You could have some code along the lines of;

Me.YourMemoField = Replace(Me.YourMemoField, , "**", vbCrLf & vbCrLf & vbCrLf)

This will replace every pair of asterisks with three line field giving you two blank lines in place of the asterisks.

 

[Urbane]

Thanks for your help John.

I'm not very good with code... so far I've only played around with it for on click and on event procedures. Therefore in order to run this code I made a new command button on a form that is related to the table, and used your code with an on click event.

It returned the following error:

"Compile error: Argument not optional"

 

[John Big Booty]

Can you post the actual code you put in the On Click event?

 

[Urbane]

Certainly:

Private Sub Command182_Click()
Me.Response_to_Submission = Replace(Me.Response_to_Submission, , "**", vbCrLf & vbCrLf & vbCrLf)
End Sub

 

[John Big Booty]

Sorry my bad just noticed I had a comma that was surplus to current requirements, try;

Me.Response_to_Submission = Replace(Me.Response_to_Submission, "**", vbCrLf & vbCrLf & vbCrLf)

 

[Urbane]

The latest code returned the following error:

Run-time error '94': Invalid use of Null.

Something to do with the asterixs being confused with wildcards?

 

[John Big Booty]

I had no problem when I tested in my sandbox DB try;

ode:

Me.Response_to_Submission = Replace(Me.Response_to_Submission, CHR(42) & CHR(42), vbCrLf & vbCrLf & vbCrLf)

 

[Urbane]

Hi John,

I copied and pasted that text exactly and received the same error.

Debugger highlights that entire line.

 

[John Big Booty]

Can you post a copy of your DB.

 

[Urbane]

The database contains loads of personal and confidential information so I really can't post a copy unfortunately.

Confidential information about our processes could be determined just from the database itself, even without data.

 

[CJ_London]

If you are taking vast amounts of data, is it possible you have exceeded the maximum size of a memo field? (Approx 64k characters equates to about 8 pages of text).

Also, is the memofield set as Rich Text? If so this may be the problem since (at least from 2007) this is HTML format so instead of vbCrLf you would use <div>LUBF1 Test response</div>. Recommend it is changed to plain text if this is the case.

Also John, I don't think your solution is quite right - you will end up with lots of vbcrlf's. I think you need to do it in stages.

Replace(Me.Response_to_Submission,"** ","|")  '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 
 
While instr(Me.Response_to_Submission,"||") ' 2 pipes
    replace(Me.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 
Wend
 
Replace(Me.Response_to_Submission," |",vbcrlf & vbcrlf & vbcrlf,2) 'Step 3: replace space and single pipe with linefeeds - but not one right at the beginning if it exists
Replace(Me.Response_to_Submission," |","",,1) 'Step 4: replace space and single pipe at the beginning if it exists

Only problem might be if the real test response data has and needs the pipe character in which case an alternative character would need to be used

 

[CJ_London]

Sorry - forgot to say re Null.The only variable in the row that could be Null is Me.Response_to_Submission - I presume you have checked you haven't got a null value in there? Use debug.print Me.Response_to_Submissionon the line before and look in your immediate window to see what is there

 

[Urbane]

Thank you very much CJ, that looks like exactly what I'm looking for.

However I keep getting the null error. I inserted that line of code but I'm not sure what it did for me?

I ran a query:

SELECT Register.Response_to_Submission, Register.ID
FROM Register
WHERE (((Register.Response_to_Submission) Is Null));

And it didn't return any null values. So it looks like that field is fully populated

This is your code exactly as I inserted it into my form:

Private Sub Command182_Click()
'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
Debug.Print Me.Response_to_Submission
Me.Response_to_Submission = Replace(Me.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(Me.Response_to_Submission, "||") ' 2 pipes
    Me.Response_to_Submission = Replace(Me.Response_to_Submission, "  ", " ")
Wend
'Step 3: replace space and single pipe with linefeeds - but not one right at the beginning if it exists
Me.Response_to_Submission = Replace(Me.Response_to_Submission, " |", vbCrLf & vbCrLf & vbCrLf, 2)
'Step 4: replace space and single pipe at the beginning if it exists
Me.Response_to_Submission = Replace(Me.Response_to_Submission, " |", "", , 1)
 
End Sub

EDIT - I should note, the field doesn't have more than 64k characters and it's access 2003, so no rich text as far as I'm aware.

 

[CJ_London]

Hi Urbane,

A number of things to try

1. Have you tried debugging?

Step through each part of the code and see where the error occurs - to do this, click on the grey vertical bar to the left of the code window on the same line as the debug.print and a maroon dot will appear in the bar, then go back to the form and click your button to run the procedure. The VBA window will reappear with the debug line highlighted in yellow. At this point it has not run so press the F8 button to advance one line - the value in Response_to_Submission should then appear in the immediate window (see below), continue to press F8 until the error occurs or the procedure finishes. If the erro occurs on leaving the debug line then Response_to_Submission has to be null.

2. With regards that line of code, I presume you mean Debug.Print?

When that bit of code runs, whatever you are debugging appears in the immediate window - if nothing appears it implies a null value. The immediate window can be opened by going to View>Immediate Window in the VBA menu - normally the window appears at the bottom of the screen.

You can modify it to Debug.Print "XX" & Response_to_Submission & "XX" so if you see XXXX in the window then you have a zero length string.

Also whilst the code is running you can hover over a variable with the mouse and it will show you the contents - and Response_to_Submission is the only variable - it may be that for some reason it is not assigned and therefore Null.

3. You can trying modifying the first replace with
Me.Response_to_Submission = Replace(nz(Me.Response_to_Submission,""), "** ", "|") which turns a null into a zero length string


4. Did you check about Rich Text?

5. Also try running this sql and see what you get - let me know what the smallest and largest numbers are if there is still a problem after all of the above has been tried:

SELECT len(Response_to_Submission) as FieldLength
FROM Register;

 

[Urbane]

Wow! Thank you so much!!! I had no idea how to step through code like that, and view the results! So very useful!

I think we're almost there on this.

Because I attached that button to a form that showed each record in the table individually, and I have a bit of code in the form that loads the form to a new record on open... it was trying to run the code on the new record which of course, is blank! When I go to a record that is populated, it works perfectly.


Could you please help me with one final issue?

1. The code works flawlessly whenever I click the button, but it's only working on each individual record. I would like it to update all records in the table at once. How do I get it to run like that?

Presumably I need to have it run, seperate to that form but I'm not sure how to do that.

Thank you!

For the record, here's the code that ended up working:

'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
Debug.Print Me.Response_to_Submission
Me.Response_to_Submission = Replace(Me.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
Debug.Print Me.Response_to_Submission
While InStr(Me.Response_to_Submission, "||") ' 2 pipes
    Me.Response_to_Submission = Replace(Me.Response_to_Submission, "||", "|")
Wend
'Step 3: replace space and single pipe with linefeeds - but not one right at the beginning if it exists
Debug.Print Me.Response_to_Submission
Me.Response_to_Submission = Replace(Me.Response_to_Submission, "|", vbCrLf & vbCrLf & vbCrLf, 2)
'Step 4: replace space and single pipe at the beginning if it exists
Debug.Print Me.Response_to_Submission
Me.Response_to_Submission = Replace(Me.Response_to_Submission, "|", "", , 1)
 
End Sub

 

[CJ_London]

Hi

Not quite so straightforward because it is a memo field but I would try the following:

1. Create a new form and add a button
2. In the On Click event add the following code

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
    'Debug.Print Me.Response_to_Submission
    rst.fields("Response_to_Submission")= Replace(rst.fields("Response_to_Submission"), "** ", "|") '(Note I have corrected " ** " to "** " 
 
    'Step 2: replace 2 pipes with 1 until only one remains as a marker should leave you with LUBF1 Test response |LUBF2 Test response
    'Debug.Print Me.Response_to_Submission
    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
    'Debug.Print Me.Response_to_Submission
    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
    'Debug.Print Me.Response_to_Submission
    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

As you will see, basically the same code but with reference to the table rather than a form

You might want to reinstate the debugs and step through the first few records to make sure it is working OK. Once happy, click on the maroon dot to remove it, then comment out the debug lines and press F5 to continue the code uninterupted

Please tick the thanks box if you have found this post usefull

Good luck!

 

[Urbane]

Good on ya CJ, that's working exactly as I hoped it would! Thank you so much for all your help on this!!

Had to make a couple of changes, first I needed to add an rst.edit before the first replace to avoid a runtime error.

I also had to return that space infront of the " ** ". . Don't quite understand it myself, but with the space it works and without it doesn't.

So for anyone else who comes across this, the final code was:

Dim Mdb As Database
Dim Rst As DAO.Recordset
 
Set Mdb = CurrentDb
Set Rst = Mdb.OpenRecordset("SELECT * FROM Register")
While Not Rst.EOF
 
    Rst.Edit
    Rst.Fields("Response_to_Submission") = Replace(Rst.Fields("Response_to_Submission"), " ** ", "|")
 
    While InStr(Rst.Fields("Response_to_Submission"), "||") 
        Rst.Fields("Response_to_Submission") = Replace(Rst.Fields("Response_to_Submission"), "||", "|")
    Wend
 
    Rst.Fields("Response_to_Submission") = Replace(Rst.Fields("Response_to_Submission"), "|", vbCrLf & vbCrLf & vbCrLf, 2)
 
    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

 

[CJ_London]

Glad to help

Re the space - if you have " ** ** ** ** ** " and just replace "**" with the marker you get " | | | | " which leaves double spaces.

Replacing " **" gives you "|||| " - no spaces in between

So when the loop is run replacing "||" with "|" you end up with "| " - sure you can work the rest out

Incidentally it should be "** " rather than " **" - has an affect of the first record

 

[thirumurugan]

Please help! I need to remove extra line bracke from a memo field

hi,


Memo Field:

wwww


DRAWING NOS14333-21137-V1200-DS-A4-0081_0



Required Result Memo

wwww
DRAWING NOS14333-21137-V1200-DS-A4-0081_0