-
Removing Leading Zeros from column
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
Last edited by Bexley Durden; 07-13-2004 at 09:05 AM.
-
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.
-
Excellent, worked a treat!
Many thanks my friend.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|