Solved

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

Posted on 2013-06-11
2
697 Views
Last Modified: 2013-06-13
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
0
Comment
Question by:Tom Winslow
2 Comments
 
LVL 17

Accepted Solution

by:
Kent Dyer earned 500 total points
ID: 39239932
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
 

Author Comment

by:Tom Winslow
ID: 39240887
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now