Solved

Delete spaces from field

Posted on 2006-06-27
2
248 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

929 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

8 Experts available now in Live!

Get 1:1 Help Now