Solved

Delete spaces from field

Posted on 2006-06-27

Microsoft Access

1 solution

Medium Priority

282 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

Comment

Question by:sarahgal

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 16

Accepted Solution

by:GreymanMSC

GreymanMSC earned 2000 total points

ID: 169985662006-06-27

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;

Author Comment

by:sarahgal

ID: 169986662006-06-27

the update statement works perfectly. thanks GreymanMSC.

appreciate the quick response....

Featured Post

Question has a verified solution.

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

William Moulton Marston’s DISC Matrix applied to Email Etiquette

Article by: Dermot

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…

Finding the Office Templates Folder

Article by: Helen

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …

MS Access – Passing a String Value within a String Argument

Video by: Jim

In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

Microsoft Access

Bar Graphs in an Access Query

Video by: crystal

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …