Sort Order

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

Hi, I have created a form the primary key field is clientID and type is
text. We have entered around 400 records. We noticed that clientid filed is
not in order any more. Records should appear as:
1
2
3
4
so on but they are appearing like this:
1
10
100
101
102
103
....
109
11
110
111

In the text box property (under table design) it is set as Indexed = Yes and
No dups.
How can I fix them? thanks
 
Note that in Text ordering "109" is lower than "11" in ascending order since
in comparing Text, Access compares the first letter of the 2 values. If the
first letter is the same ("1" in this case), then Access compares the second
letter of the 2 values. In this case, "0" is smaller than "1". As soon as
the order is established, the rest of the characters are ignored.

Hence, "109" is listed before "11".

You can either change the Table Field to Numeric (Integer or Long).
Alternatively, you can use a Calculated Field

NumericClientID = CLng(clientid)

in the Query and order by this Calculated Field. You can then use this
Query as the RecordSource for your Form.

HTH
Van T. Dinh
MVP (Access)
 
Van,

I have a similiar situation to to JD's, but the problem revolves around IP
addresses; the IP is stored as the entire ip address (xxx.xxx.xxx.xxx) and
also is not formated (so the actual entry could be (x.x.x.x). In many of my
forms, it would be nice to sort on the IP address but run into the same text
vs. numeric sort issue described here. Do you have a suggestion?

Thank you very much for sharing your insight.
 
Split the IP address into 4 numeric fields, and hold it that way in your
table.. Sort on each field in a query. Combine the numbers back into the
correct format for display.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top