Trimming Data

  • Thread starter Thread starter K
  • Start date Start date
K

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
 
K said:
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!!
Click to expand...

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.
 
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
 
K said:
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!!
Click to expand...

If the comma is always the last character...

UPDATE YourTableName
SET [salutation] = Left([salutation], Len([Salutation])-1)
Click to expand...

and if there isn't always a comma, add

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