Remove a space in the middle of a String?

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

Hi guys,
Can you advise me on how I can remove a single space from
the middle of a string? I have a large number of records
to import and many of them (but not all) have a space in
the middle of the field. For example:

3301TH
3301XH
3301 X
3301 T

The top two records are fine but the other two need to
read:

3301X
3301T

I can't see how to remove the space. Can you help?

Kind regards,

Lee
 
Lee-

It's easy if you have Access 2002 or 2003. First, import your data (I
assume you don't want to update the original source). Run the following
query on your table:

UPDATE MyTable
SET MyField = Replace([MyField], " ", "")

... where MyTable is the name of your table and MyField is the name of the
field.

In Access 2000, it's a bit trickier:

UPDATE MyTable
SET MyField = Left([MyField], 4) & Mid([MyField], 6)
WHERE InStr([MyField], " ") <> 0

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
If you have Access 2000 or later, use the Replace() function in an Update
query.

1. Create a query into your table.

2. Change it to an Update query (Update on Query menu).
Access adds an Update row to the query.

3. In the Update row under your field, enter:
Replace([MyField], " ", "")
where "MyField" represents the name of your field.

4. Run the query. No need to save.
 
Thanks for your help on this.
I've just moved over to using Access 2002 from 97 so
wasn't familiar with the Replace function....that's
great!!!

Best regards,

Lee
 
Back
Top