RTrim help (I think)

[Gareth]

I am new to Access and have the following problem with a db.

Customer names were imported into the db and because we are now just
creating queries, etc we have discovered the problem.

The names of some of the customers are not identical. By this I mean that
some have an additional space, for example:

"Gareth Evans"
"Gareth Evans "

Obviously Access will see this customer as 2 customers even though there is
only 1. I have looked at help but cannot work out how to use RTrim to
remove all thes spaces in one go (there are approx 5,000 records so it would
be useful if it could be done using code).

Any help gratefully received.

Gareth

 

[Dirk Goldgar]

I am new to Access and have the following problem with a db.

Customer names were imported into the db and because we are now just
creating queries, etc we have discovered the problem.

The names of some of the customers are not identical. By this I mean
that some have an additional space, for example:

"Gareth Evans"
"Gareth Evans "

Obviously Access will see this customer as 2 customers even though
there is only 1. I have looked at help but cannot work out how to
use RTrim to remove all thes spaces in one go (there are approx 5,000
records so it would be useful if it could be done using code).

Any help gratefully received.

Gareth

You can use the function in an update query, with SQL similar to this
(substituting your own table and field names):

UPDATE tblCustImport
SET CustomerName = Trim(CustomerName);

 

[Terry]

I am new to Access and have the following problem with a db.

Customer names were imported into the db and because we are now just
creating queries, etc we have discovered the problem.

The names of some of the customers are not identical. By this I mean that
some have an additional space, for example:

"Gareth Evans"
"Gareth Evans "

Obviously Access will see this customer as 2 customers even though there is
only 1. I have looked at help but cannot work out how to use RTrim to
remove all thes spaces in one go (there are approx 5,000 records so it would
be useful if it could be done using code).

Any help gratefully received.

Gareth

I'm no expert but you could use an Update query along the lines of:

Field: YourCustomerFieldName
Table: YourTableName
Update To: Trim([YourCustomerFieldName])

'Trim' will remove leading _and_ trailing blanks.

You might also want to check for records with more than a single space
between first name and last

Hope that helps
Terry