Trim leading zeros

[Guest]

Hi, found ways to add leading zeros but is there an easy method to remove
them from a text field? I have data like the following examples and want to
trim all the leading zeros:

0000002
0000102
0000016

Just for reference the field does contain valid leading zeros too (hence its
a text field and a not a number type). The table holds data for lots of
countries, I only want to remove the leading zeros for one particular country.

Thanks in advance for any help.
Sue

 

[Guest]

Use the Val function, e.g.
Val(txtField)

 

[Guest]

Hi, Sue.

Hi, found ways to add leading zeros but is there an easy method to remove
them from a text field?

If you'd like to remove them from displaying in the text box, then you could
alter the column in the query to remove the leading zeros. However, the
records with leading zeros would remain the same in the table. For example:

SELECT IIF(Country = "Canada", Val(Code), Code) AS NewCode, Country
FROM tblCodes;

If you'd like to remove them permanently from your table for just this
country, then use an UPDATE query. For example:

UPDATE tblCodes
SET Code = IIF(Country = "Canada", Val(Code), Code)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks guys

Hi, Sue.


If you'd like to remove them from displaying in the text box, then you could
alter the column in the query to remove the leading zeros. However, the
records with leading zeros would remain the same in the table. For example:

SELECT IIF(Country = "Canada", Val(Code), Code) AS NewCode, Country
FROM tblCodes;

If you'd like to remove them permanently from your table for just this
country, then use an UPDATE query. For example:

UPDATE tblCodes
SET Code = IIF(Country = "Canada", Val(Code), Code)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.