access thread: Sorting text numbers in order
Message #1 by simonds@m... on Wed, 23 Jan 2002 15:54:12
|
|
I have a database that has a list of policies for a manual. Each section,
sub-section, and sub sub-section is numbered accrodingly, e.g., 80.013.003.
I want to order the sections numbers in sequential order, but what I get
is anything with a one comes first, then the twos, etc.
For example:
80.013.010
80.013.100
80.013.120
80.013.020
80.013.200
...
They should appear in proper order (i.e., 10, 20, 100, 120, 200...). I
know that the data type was originally a number, but was changed to text
because as a number it left out the leading zeros (three digit lengths are
needed). The table data has the three digit numbered stored, and each
section, sub-sedtion, and sub sub-section is stored in a seaprate field.
Is there a way to order these accordingly?
Regards,
Eric
Message #2 by Joe Constant <jconstant@h...> on Wed, 23 Jan 2002 08:01:14 -0800
|
|
SELECT MyTextNumber
FROM MyTable
ORDER BY CLng([MyTextNumber]);
Joe
-----Original Message-----
From: simonds@m... [mailto:simonds@m...]
Sent: Wednesday, January 23, 2002 7:54 AM
To: Access
Subject: Sorting text numbers in order
I have a database that has a list of policies for a manual. Each section,
sub-section, and sub sub-section is numbered accrodingly, e.g., 80.013.003.
I want to order the sections numbers in sequential order, but what I get
is anything with a one comes first, then the twos, etc.
For example:
80.013.010
80.013.100
80.013.120
80.013.020
80.013.200
...
They should appear in proper order (i.e., 10, 20, 100, 120, 200...). I
know that the data type was originally a number, but was changed to text
because as a number it left out the leading zeros (three digit lengths are
needed). The table data has the three digit numbered stored, and each
section, sub-sedtion, and sub sub-section is stored in a seaprate field.
Is there a way to order these accordingly?
Regards,
Eric
Message #3 by "David Adams" <davidadams00@h...> on Wed, 23 Jan 2002 11:11:39 -0500
|
|
I do not know if you can convert the text string 80.013.010 to a number.
You can always store the three parses of the value (80 13 10) in three
separate numeric fields and handle your select in the manner that Joe
suggested.
SELECT MyTextNumber
FROM MyTable
ORDER BY [ParseValue1],[ParseValue2],[ParseValue3]
Good luck.
David Adams
----- Original Message -----
From: "Joe Constant" <jconstant@h...>
To: "Access" <access@p...>
Sent: Wednesday, January 23, 2002 11:01 AM
Subject: [access] RE: Sorting text numbers in order
> SELECT MyTextNumber
> FROM MyTable
> ORDER BY CLng([MyTextNumber]);
>
>
> Joe
>
>
> -----Original Message-----
> From: simonds@m... [mailto:simonds@m...]
> Sent: Wednesday, January 23, 2002 7:54 AM
> To: Access
> Subject: Sorting text numbers in order
>
>
> I have a database that has a list of policies for a manual. Each section,
> sub-section, and sub sub-section is numbered accrodingly, e.g.,
80.013.003.
>
> I want to order the sections numbers in sequential order, but what I get
> is anything with a one comes first, then the twos, etc.
>
> For example:
> 80.013.010
> 80.013.100
> 80.013.120
> 80.013.020
> 80.013.200
> ...
>
> They should appear in proper order (i.e., 10, 20, 100, 120, 200...). I
> know that the data type was originally a number, but was changed to text
> because as a number it left out the leading zeros (three digit lengths are
> needed). The table data has the three digit numbered stored, and each
> section, sub-sedtion, and sub sub-section is stored in a seaprate field.
>
> Is there a way to order these accordingly?
>
> Regards,
> Eric
>
>
>
Message #4 by "Gregory Serrano" <serranog@m...> on Wed, 23 Jan 2002 16:31:03
|
|
Eric,
<< I have a database that has a list of policies for a manual. Each
section, sub-section, and sub sub-section is numbered accrodingly, e.g.,
80.013.003.
I want to order the sections numbers in sequential order, but what I get
is anything with a one comes first, then the twos, etc. >>
Don't store the policy number as the string "XXX.XXX.XXX". Instead store
them as three separate numerical fields called Section, Subsection and
Subsubsection. In your query, sort by Section first, then subsection,
then section. Since they're numbers, they'll sort accordingly. Then
create a field in your query (don't sort it) as such:
Policy: Format(Section, "000") & "." & Format(Subsection, "000") & "." &
Format(Subsubsection, "000")
The policy number is never stored anywhere. It's always reconstructed
when needed from its three parts, which are stored as numbers.
Greg
|
View Cart
