Record Order

[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

 

[Allen Browne]

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.

 

[Brendan Reynolds]

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]))

 

[Arvin Meyer]

You can either build a custom sort column, or alias a column in a query to
strip off the first 2 characters then convert the balance to a number and
sort on that. Something like:

Expr1: CInt(Mid([TestName],3))
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Troy]

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