Record Order

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

I have a table where the primary field items are like,
TS1
TS2
TS3
and so on. The problem is it shows up in this order,
TS1
TS10
TS11
TS12
and so on. How can I force it to go in the order like
above? This not so important in my table, but really
important in my form.

Thanks,

Troy
 
The problem arises because the data is not atomic (i.e. you have two
different things in one field). Split the data. Use the 2 fields combined as
the primary key if you wish.

Alternatively, if the prefix is always two characters, base the form on a
query, with this in its ORDER BY clause:
Left([pk],2), CLng(Mid([pk], 2))

If the text part is of indeterminate length, you have more work to do, using
Instr() to identify where the split happens.
 
I'm assuming that these values follow some logical pattern, for example the
first two characters are always non-numeric and the remainder always
numeric. That being the case there are at least three possibilities.

a) Best solution, use separate fields, text field for the text part and
numeric field for the numeric part.

c) Include leading zeros as necessary (TS01, TS02, TS03, etc). A potential
problem is you need to be sure of the maximum number of digits that will
ever be needed.

d) Use a query to separate the text and numeric parts of the data, e.g.
SELECT * FROM SomeTable ORDER BY Left$([SomeField]), 2),
Val(Mid$([SomeField, 2]))
 
Troy said:
I have a table where the primary field items are like,
TS1
TS2
TS3
and so on. The problem is it shows up in this order,
TS1
TS10
TS11
TS12
and so on. How can I force it to go in the order like
above? This not so important in my table, but really
important in my form.

Thanks,

Troy
Click to expand...

Let me add to what the others have said. The list is in Alpha order.
In a text field the normal sort starts with the first character and the then
next character, be that character one of the 26 letters or 10 numbers or ?
many other characters.

What the best solution may be will depend on a number of factors. In
some cases you can use one of the tricks suggested, but a better solution
may be to reconfigure the data. For example you may want to break it up
into alpha and numeric parts in two different fields. Another suggestion is
to change the way you enter the data. Could you use TS01. TS02, TS03 ...
TS10, TS11 etc? If you may go over 99 consider TS001

I have that problem with data I had to import each year. It had over
4,000 records and I automated the task of changing all those "1XXXX" ...
"9XXXXX" into 01XXXX .. 09XXXX.

Good Luck
 
Thanks guys and gals! I just added a zero before the
number. How come I didn't think of that to start with?
lol This group rocks!

thanks again,

Troy
 
Back
Top