Trimming Data

[K]

I have a table in an Access 2000 database. The field i am
having a problem with is a salutation field, some users
have entered a comma after the name and some haven't, how
do i get rid of all the commas after the text in this
field so they all look the same. I would do this manually
be there are 71,000 records!!

Thanks very much

 

[Rick Brandt]

I have a table in an Access 2000 database. The field i am
having a problem with is a salutation field, some users
have entered a comma after the name and some haven't, how
do i get rid of all the commas after the text in this
field so they all look the same. I would do this manually
be there are 71,000 records!!

If the comma is always the last character...

UPDATE YourTableName
SET [salutation] = Left([salutation], Len([Salutation])-1)

Always test something like this on a copy of your table first.

 

[Howard Brody]

First thing you do is save another copy of your table,
just in case.

Then, create a new Update query in Design view and use the
following:

Field: [Salutation]
UpdateTo: Mid([Salutation],1,(Len([Salutation])-1))
Criteria: Like "*,"

I usually run a Select query with the criteria first, to
make sure I'm getting the fields I want to.

Hope this helps!

Howard Brody

 

[John Nurick]

I have a table in an Access 2000 database. The field i am
having a problem with is a salutation field, some users
have entered a comma after the name and some haven't, how
do i get rid of all the commas after the text in this
field so they all look the same. I would do this manually
be there are 71,000 records!!

If the comma is always the last character...

UPDATE YourTableName
SET [salutation] = Left([salutation], Len([Salutation])-1)

and if there isn't always a comma, add

WHERE (Right([Salutation],1) = ",")