change default numbering in access all versions

[questions]

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 (e-mail address removed)

 

[John Spencer]

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

 

[Jeff Boyce]

Tom

Are you talking about numbers (e.g., integers, singles, ...) or characters
(i.e., digits that LOOK like numbers but are actually text)? I suspect the
latter.

Take a look at the data type of the field(s).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/