Trimming leading zero's from a database

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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."
 
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
 
Tom said:
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
Click to expand...
 
Back
Top