Solved

Delete spaces from field

Posted on 2006-06-27

Microsoft Access

1 solution

277 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 500 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

Netflix Phishing Scam – Don’t Get Fooled

Article by: Dermot

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.

Creating an Access Database Taskbar Item in Windows 7 through 10

Article by: Helen

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.

Microsoft Access

Access Desktop Databases – A Quick Tour

Video by: Jim

What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

Microsoft Access

MS Access – Repeating Sub-Report Headings on Each Page

Video by: Jim

In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…