Trimming leading zero's from a database

[Tom]

I have a column in Access 2007 that shows a number like "120" or "0120" which
is a code that means the same thing.

I would like to eliminate the leading zero for sorting purposes.

Thank you in advance for your help in this matter

 

[Al Campagna]

Tom,
You can use the Val function, and an Update query against your table.
= Val(YourTextNum)

If some of your YourTextNum values are null...
=NZ(Val([YourTextNum]),0)
will value the nulls as 0.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Clif McIrvin]

If that's a numeric data type it sorts on value so 0120 and 120 are
identical; just a formatting (display) difference.

But, since you ask the question, it would seem that this is a text
field, which would alter the sort.

Are the values in this column *always* digits (numbers)?

If so, write a query with a calculated column and sort the query on that
(numeric) column. You can clear the "show" checkbox and hide the
calculated value.

NumericSort: [MyAlphaCode]+0

If MyAlphaCode contains both digits and non-digits the process gets a
bit more complicated.

After I wrote the above I went to Google and asked for "Access numeric
sort order".

Two of the hits looked particularly relevant to your question:

http://www.tutcity.com/view/sort-records-on-numeric-values-stored-in-a-text-field.12063.html

http://www.eggheadcafe.com/conversation.aspx?messageid=33419491&threadid=33419490

 

[huangda 晋江兴达机械]

I have a column in Access 2007 that shows a number like "120" or "0120"
which
is a code that means the same thing.

I would like to eliminate the leading zero for sorting purposes.

Thank you in advance for your help in this matter