How to remove the leading zeros in a table

[accessfever]

Hi all,

I have 2 tables. One table has a part column with leading zeros and another table has a part column with no leading zeros. The two tables are downloaded in different sources. Currently, I go in the table with leading zeros to find & replace in order to get rid of those zeros. I wonder if there is an Access command can remove leading zeros in one update query.

Please help.

 

[GohDiamond]

Here's one way:

I use the function VAL([fieldname]) to remove them.

Employee ID 00881 becomes Employee IDnew 881 (Numeric value now)

In the query add a column:
Employee IDnew:Val([Employee ID])

Use Employee IDnew as your key.

This will convert the field with the leading zeros to a number value.

Do it for both files and that will cure any Datatype mismatch errors and let you match the IDs as if they were serial numbers in a general number format

Cheers!
Goh