Sort in a Query

[Alastair]

Dear All,

How do you sort a quey so that the following Table is
sorted properly:

Table: Table1
Field: Field1
Values: A1, A10, A3, A8, B233b, B233, B23a

I would like this sorted in alphanumerical order like:

A1, A3, A8, A10, B23a, B233, B233b

I have no knowledge of the number of letters or number
combinations that can be used, only that I would like them
alphanumerically sorted.

I have previously posted this question and received a
couple of replies and now have misplaced the responses so
I am sorry for those who answered my previous post.

Thanks again.

Alastair

 

[Allen Browne]

One of the basic rules of data normalization is to store only one thing in a
field (atomic values).

Your field clearly contains 2 or 3 different kinds of things: a 1-char
prefix, a numeric value, and sometimes a suffix. Break the field into atomic
values, and your problem disappears.

If you do not like that solution and believe that you were previously given
a better answer, go to http://groups.google.com and choose the Advanced
Search. Search groups:
comp.databases.ms-access, microsoft.public.access.*

 

[Kagsy]

Allen,

Thanks for the reply. I fully understand what you are saying but how can I
manage this in my example. I have a table called Files whose PrimaryKey is
FileNo where clients can create any combination of FileNo to match in with
their own current naming conventions. So I may get A1, Mac1 or 11Mac.

If I neither know the combination of Alpha or numeric values, how can I
create a sort for this field or even break it down to smaller components as
you suggest. I wouldn't think that a field for alternating alpha and numeric
values would work that well from a design standpoint.

How can I have the dual functionality of allowing the user to enter in what
they want and at the same time see a sorted list of their selected client's
files?

Thanks again for your help.

Alastair MacFarlane

 

[Allen Browne]

If these are just file names and the numeric portion has no meaning, then I
would have thought that text-based sorting would be appropriate, i.e.
A1, A10, A3, A8, ...
Guess I'm missing something here.

If the numeric portion is somehow significant, you could write a function to
parse it out with Instr(), Mid(), Len(), IsNumeric() etc, but it would be a
dog's breakfast with all the speed of a sloth.

 

[Alastair]

Allen,

The truth is that I would like an easy way of sorting a
FileNo list in a MScomctl.Lib Treeview, but it may be
better that I just integrate in a search for the fileno in
a textbox and go to the relevant fileno. There may be 10s
of files for a client in my system and one client can have
many files.

Thanks for your help, but it might be just be too much
(pointless?) work undertaking the Mid/Instr, etc sort.

Alastair