Solved

Delete spaces from field

Posted on 2006-06-27
2
269 Views
Last Modified: 2011-10-03
I need to be able to run a query that deletes all spaces contained within a field.  The table is tblSymbol and has a field symCode.  

Field symCode contains a mixtureof alpha characters and spaces.  I need to delete all spaces from within the field.  lets say I have someting like

AA B
CD R
F GG
G H D
FFD (space) (space) etc

I need to end up with

AAB
CDR
FGG
GHD
FFD (with no following spaces)  in fact no following spaces for any of the symbols in the field.

need a quick solution here, thanks

Any suggestions
thanks
0
Comment
Question by:sarahgal
2 Comments
 
LVL 16

Accepted Solution

by:
GreymanMSC earned 500 total points
ID: 16998566
Use the following sql statement to update the table

  Update tbltblSymbol set symCode=Replace(symCode, " ", "") where InStr(1, symCode, " ")>0;

Use the following to simply retrieve the values stripped of spaces,

  Select Replace(symCode, " ", "") as symCodeDespaced from tbltblSymbol;
0
 

Author Comment

by:sarahgal
ID: 16998666
the update statement works perfectly.  thanks GreymanMSC.

appreciate the quick response....
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

685 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