Solved

Delete spaces from field

Posted on 2006-06-27

Microsoft Access

1 solution

275 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

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 …

Automating Microsoft Excel from Access (Part II)

Article by: Dale Fye

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.

Using a SQL Server View from Within Access

Video by: TechMommy

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…

Secure Portal Encryption

Video by: Dermot

With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …