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!
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!
Last edited: