Remove extra characters in records 2

[mrkshpntf]

I have a table (150K records) in which the primary key field (has to be text) has records with extra zeros to the left.

I need only six digits from the right and it has to be in the text datatype as I am using it in a query and form.

I need to get rid of the extra zeros so that all the records (historical) are in six-digits.

Please help

 

[PHV]

UPDATE yourTable
SET yourField = Right([yourField], 6)
WHERE Len([yourField]) > 6

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrkshpntf]

PHV,

Thanks for your prompt response.

Excuse me, I am reacquainting myself with MS-Access and this might sound like a stupid question: How and where do I run the code you have given?

Thanks,
mrkshpntf

 

[lespaul]

start a new query, switch from the query design grid to the SQL view and paste the query above; replace the table and field names and run the query.

I would suggest a SELECT query to verify that you are updating what you expect:

SELECT yourField, Right(yourField, 6) FROM TableName

AND backup the table BEFORE running the UPDATE query.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins