I'm new with Access. In my data table, I
have a field which I use to put No. of my
data (the input will be 1, 2, 3, ...., 40).
Then I use this field to sort my data, but
what I got is 1 then 10 then 11, 12, ,,,,,
not like 1,2,3, 4....
Is there no "natural order" to your data so you have to reintroduce the
concept of "record number" which does not conform to relational database
design principles and was purposely omitted from Access?
By definition, records in tables in a relational database are unordered. You
choose appropriate fields by which to order them in the Queries you use to
retrieve them (or in Access, via the specified sorting and grouping choices
in a Report).
Can anyone show me how to correct this?
Your "number" Field appears to have been defined as Text. That is often the
best choice for an identification field but you shouldn't expect it to sort
as though it were a number. You've probably noticed another difference from
numeric fields, that is, it displays left-justified by default while numbers
display right-justified by default.
If that number is somehow important to you, and always (ALWAYS) consists
only of numeric characters, you can create in your query a calculated field
on which to sort using the Val function or the CInt or CLng functions... in
this case, the calculated field can be shown or hidden, as you prefer.
Or, if you are dead-certain that some user won't be coming back to you
complaining that "the system won't let me define my new widget, product
number 2185XVQ", you could simply redefine the field as a numeric type of
your choosing. That is, you are wagering that no one will EVER want an
identification "number" that has some alphabetic components. And NEVER is a
long, long time.
Larry Linson
Microsoft Access MVP
