Removing Leading Zeros from column

[Bexley Durden]

Hi All,

Could anybody tell me how to remove all the leading zeros from a column of data using an update query??
I've tried changing the data type in the column to an integer and also using the right function, but as there are variable amounts of leading zeros this doesn't work too well.

Any suggestions??

Many Thanks

[kpinocchio]

Hi,

Create an update query and update the values in the column using the following formula. I will assume the column is named Field1 for the example:


Str$(Val([Field1]))


The Val function should convert it to a numeric value, which will get rid of the leading zero. The Str$ function converts it back into a string value so it can be saved back into the table.

But this will only work on values that only contain numeric values - if any rows contain non-numeric data, like embedded spaces or hyphens it will error.

[Bexley Durden]

Excellent, worked a treat!

Many thanks my friend.