MSAccess query design to Search and Replace "#" (pound sign)

I have a JD Edwards table where an upload caused extra data to be added to two (2) description fields. I need to search for 'MSA#350298' on the description fields and replace with '' (nothing). I can search for the pound sign by using brackets. *[#]* but I cannot search for the entire string.

My plan is to use the search and replace feature in Microsoft Access. Is there a better process?

tw
Tom WinslowAsked:
Who is Participating?
 
Kent DyerConnect With a Mentor IT Security Analyst SeniorCommented:
How about using Chr(35) ?  You should be able to do something like this..
UPDATE TABLE
SET SOMEFIELD = REPLACE(SOMEFIELD,CHR(35),"")
WHERE SOMEFIELD = SOMEVALUE

Open in new window


HTH,

Kent
0
 
Tom WinslowAuthor Commented:
The field I have to delete is " OGGLE MCM #5071A58"  and it has a leading space. there are over 17k description lines that contain this 'trash' data that I have to remove.

SAMPLE DATA:

PROC TO SECURE 3 JAW CPLGS TO SHAFT OGGLE MCM #5071A58
FEED WDO REPLACEMENT PROC 2.4M VISAT & 3.8M DPC/DPK OGGLE MCM #5071A58
PROCESS SPEC F/BONDING SILI- CONE RBR TO AL & SS OGGLE MCM #5071A58
ANT INSTL 3.8DPC/K  OGGLE MCM #5071A58
ANT HARWARE KIT 3.8M (STD)  OGGLE MCM #5071A58
HDW KIT PIPE MT INSTL 3.8M DPC/K OGGLE MCM #5071A58
REF INSTL 3.8M HDW KIT STD  OGGLE MCM #5071A58
REF INSTL 3.8M BOX 2 RADIAL BE AM + MISC OGGLE MCM #5071A58
REF INSTL 3.8M BOX 3 PNL ASSY  OGGLE MCM #5071A58
PNL 3.8M  OGGLE MCM #5071A58
PNL 3.8M PTD  OGGLE MCM #5071A58
PNL 3.8M  OGGLE MCM #5071A58
PNL 3.8M PTD  OGGLE MCM #5071A58
PNL ASSY 3.8M  OGGLE MCM #5071A58
PNL ASSY 3.8M PTD  OGGLE MCM #5071A58
PNL ASSY 3.8M  OGGLE MCM #5071A58
PNL ASSY 3.8M PTD  OGGLE MCM #5071A58
PNL SKIN TRIMMED TO SZ 3.8M  OGGLE MCM #5071A58
PNL SKIN TRIMMED TO SZ 3.8M  OGGLE MCM #5071A58
PNL SKIN STRETCH FORMED 3.8M  OGGLE MCM #5071A58
PNL 3.8M  OGGLE MCM #5071A58
PNL 3.8M PTD  OGGLE MCM #5071A58
PNL 3.8M  OGGLE MCM #5071A58
PNL 3.8M PTD  OGGLE MCM #5071A58

I need to capture all of the data and delete it including the spaces.

tw
0
All Courses

From novice to tech pro — start learning today.