Re: change default numbering in access all versions

A text field is sorted in TEXT (alpha) order which means "100" comes before "2".

If you want to sort a text field in number order then you have to force the text to become a number using the Val function or CDbl.

If the text field is always numbers and only numbers and you need numerical order you might consider storing the value in a number field.

Another alternative is to store all the text numbers with leading zeroes or force leading zeroes before you sort using the format function - Format([YourField],"000000").


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

questions wrote:
two questions-
1- how can a screwy default numbering system be changed to normal ? ex: 1,2,3,4,5,6,7,8,9,10,101,102,103,1001,1002,2,3,4, and so on to a normal whole integer number, 1, 2, 3, and so on. i have a query that corrects it but for other reasons i prefer not to use it if can be avoided.
2- what triggers this occurrence, i have other numeral columns without this type of confusion. i feel it might have to do with autonumber, but if it does why aren't these other numeral columns affected also ? in design i originally classed the column as text, i tried number and even memo, all n/g, text seemed the better of all three. would appreciate response, tom mcintyre mcintyremcintyre@xxxxxxxxxxx
.

Relevant Pages

  • Re: Group Header Sorting
    ... Use the Val function: add it as a computed field to the query, and then sort ... >I have a group header that I am using to sort data on a report. ...
    (microsoft.public.access.reports)
  • RE: Report in Certain Order
    ... Thanks OssieMac for your concise and prompt answer. ... This is so the numeric sort ... Set sort for this field to Ascending. ... Val function reads the text up to last numeric and creates a real number so ...
    (microsoft.public.access.gettingstarted)
  • Re: sort numbers in a query
    ... Look at the way each aligns ... > When I try to sort in a query 10/12 Brown Street ... I have tried the Val function but it ...
    (microsoft.public.access.queries)
  • Re: Help Needed With Format Of Query Expression
    ... they will BE text and sort as text. ... the CInt or Val function, or you can calculate them instead of using the ...  Microsoft Office Access MVP ...
    (comp.databases.ms-access)