Remove Spaces

[Roger Bell]

I have inherited a Data Base where most fields have several spaces after the
data.

Can I use an Update query to clear the spaces, and if so, how would I write
the Update query?

Thanks for any help

 

[Wayne-I-M]

UPDATE TableName SET TableName.FieldName = Replace(TableName!FieldName," ","");

 

[Philip Herlihy]

I have inherited a Data Base where most fields have several spaces after the
data.

Can I use an Update query to clear the spaces, and if so, how would I write
the Update query?

Thanks for any help

You can use the Trim functions (see help) in queries to remove leading,
trailing spaces (or both). If you google for Access update queries and
look for video results, you'll see quite a few demonstrations.

One thought - if you've inherited a (presumably working) database, I'd
be rather cautious before changing too much. You can leave the data
with its extra spaces and view it without (using Trim). When I inherit
something I usually change it as little as possible, and work around it.

HTH

Phil, London

 

[John Spencer]

Be careful, that function (Replace) will remove ALL spaces in the field
so a company name like
Columbia Educational Consulting would become
ColumbiaEducationalConsulting

Far better would be to use either Trim (removes leading and trailing
spaces) or RTrim (removes trailing spaces) in the update query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

I have inherited a Data Base where most fields have several spaces after the
data.

That's very odd indeed, since Access automatically trims trailing blanks after
Text fields (the Access Text type is essentially the same as a SQL/Server
Varchar, variable length, rather than a SQL/Server fixed length Text).

It's possible to define fixed length text fields in Access but it's not easy
and rarely done. If the developer of this databse went to all that trouble
there may be a reason that they did so: be sure you have a backup before you
do anything drastic!