How do I get an alphanumeric ID to sort numerically?

[Dustin]

I have some part numbers that are unique only through letters not numbers.
The two differences are DR and DF and then the numbers. Ex. DR1240,DF1240.
How do I get them to sort numerically?

 

[Kevin B]

Create a query, select the columns you want regturned and in a blank column
enter the the following formula, replacing [FieldName] with the name of the
alpanumeric field you want to sort by:

CLng(Right([FieldName],Len([FieldName])-2))

Set the sort to Ascending or Descending, depending upon your wont, and turn
off the display check box to suprress the display of your sort column

 

[Ofer]

Create a field in the order section of the query with

Order By Mid([FieldName],3)

 

[Dustin]

Hi Kevin,
I am self-learning access, so I know what you want me to do. But what do
some of those characters in the Expression mean? Ex. CLng, Right, Len, and
-2. That way I understand how it worked. I hope I am not asking too much.

Thanks,
Dustin B

Create a query, select the columns you want regturned and in a blank column
enter the the following formula, replacing [FieldName] with the name of the
alpanumeric field you want to sort by:

CLng(Right([FieldName],Len([FieldName])-2))

Set the sort to Ascending or Descending, depending upon your wont, and turn
off the display check box to suprress the display of your sort column

--
Kevin Backmann

I have some part numbers that are unique only through letters not numbers.
The two differences are DR and DF and then the numbers. Ex. DR1240,DF1240.
How do I get them to sort numerically?

 

[Kevin B]

The CLng function converts a string to a long integer value, insuring that
you get a numberic sort from your numbers. The Right(Value, Number of
Characters) takes a text value and extracts from the right side the number of
stated characters. For example, Right(AA1222,4) would return the value of
1222. The Len(Value) function counts the number of characters in a value and
returns that number. For example, Len(123) would return a 3.

The formula uses a combination of these formulas to arrive at the result.
So in this instance, Clng(Right([PartNumber]),Len([PartNumber])-2) does the
following:

The Clng function converts the result to a long integer, the right function
extracts all the characters from the 3rd position to the end, the number of
characters to be extracted determined by the total length of the value - the
2 character positions occupied by the part number prefix letters.

Hope this helps.


--
Kevin Backmann

Hi Kevin,
I am self-learning access, so I know what you want me to do. But what do
some of those characters in the Expression mean? Ex. CLng, Right, Len, and
-2. That way I understand how it worked. I hope I am not asking too much.

Thanks,
Dustin B

Create a query, select the columns you want regturned and in a blank column
enter the the following formula, replacing [FieldName] with the name of the
alpanumeric field you want to sort by:

CLng(Right([FieldName],Len([FieldName])-2))

Set the sort to Ascending or Descending, depending upon your wont, and turn
off the display check box to suprress the display of your sort column

--
Kevin Backmann

I have some part numbers that are unique only through letters not numbers.
The two differences are DR and DF and then the numbers. Ex. DR1240,DF1240.
How do I get them to sort numerically?

 

[Dustin]

Thanks for explaining that I understand the process.
But when I tried that it, told me "The expression you entered has a function
containing the wrong number of arguements". What am I doing wrong?

The expression is, Clng(Right([PartNumber]),Len([PartNumber])-2), I am sort
ID's like DR1240 as example

The CLng function converts a string to a long integer value, insuring that
you get a numberic sort from your numbers. The Right(Value, Number of
Characters) takes a text value and extracts from the right side the number of
stated characters. For example, Right(AA1222,4) would return the value of
1222. The Len(Value) function counts the number of characters in a value and
returns that number. For example, Len(123) would return a 3.

The formula uses a combination of these formulas to arrive at the result.
So in this instance, Clng(Right([PartNumber]),Len([PartNumber])-2) does the
following:

The Clng function converts the result to a long integer, the right function
extracts all the characters from the 3rd position to the end, the number of
characters to be extracted determined by the total length of the value - the
2 character positions occupied by the part number prefix letters.

Hope this helps.


--
Kevin Backmann

Hi Kevin,
I am self-learning access, so I know what you want me to do. But what do
some of those characters in the Expression mean? Ex. CLng, Right, Len, and
-2. That way I understand how it worked. I hope I am not asking too much.

Thanks,
Dustin B

Create a query, select the columns you want regturned and in a blank column
enter the the following formula, replacing [FieldName] with the name of the
alpanumeric field you want to sort by:

CLng(Right([FieldName],Len([FieldName])-2))

Set the sort to Ascending or Descending, depending upon your wont, and turn
off the display check box to suprress the display of your sort column

--
Kevin Backmann


:

I have some part numbers that are unique only through letters not numbers.
The two differences are DR and DF and then the numbers. Ex. DR1240,DF1240.
How do I get them to sort numerically?

 

[John Vinson]

Thanks for explaining that I understand the process.
But when I tried that it, told me "The expression you entered has a function
containing the wrong number of arguements". What am I doing wrong?

The expression is, Clng(Right([PartNumber]),Len([PartNumber])-2), I am sort
ID's like DR1240 as example

An extra parenthesis sneaked in. There's actually a simpler expression
that will work - the Mid() function extracts a substring. Normally you
can say

Mid("abc123xyz", 4, 3)

to start at the 4th byte of the string and extract the next 3 - "123"
in this case. But you can leave off the length argument and it will
extract from the second argument to the end. Thus:

CLng(Mid([PartNumber], 3))

will extract "1240", and CLng will convert that text string to a Long
Integer.

John W. Vinson[MVP]

 

[Dustin]

This worked but when I ran the query it doesn't show show me the DR in front
of the number and it doesn't show me DR1241. It showed me only numbers that
ended with 0. What do I now?
And I have just noticed that I have letters on the end of some part no's as
well. How does that figure into the expression?

Thanks

Thanks for explaining that I understand the process.
But when I tried that it, told me "The expression you entered has a function
containing the wrong number of arguements". What am I doing wrong?

The expression is, Clng(Right([PartNumber]),Len([PartNumber])-2), I am sort
ID's like DR1240 as example

An extra parenthesis sneaked in. There's actually a simpler expression
that will work - the Mid() function extracts a substring. Normally you
can say

Mid("abc123xyz", 4, 3)

to start at the 4th byte of the string and extract the next 3 - "123"
in this case. But you can leave off the length argument and it will
extract from the second argument to the end. Thus:

CLng(Mid([PartNumber], 3))

will extract "1240", and CLng will convert that text string to a Long
Integer.

John W. Vinson[MVP]

 

[John Vinson]

This worked but when I ran the query it doesn't show show me the DR in front
of the number and it doesn't show me DR1241. It showed me only numbers that
ended with 0. What do I now?
And I have just noticed that I have letters on the end of some part no's as
well. How does that figure into the expression?

You need to include both the actual field (for display) *and* the
calculated field (just for sorting). Try

SortKey: Val(Mid([PartNumber], 3))

Mid() will return the string from the third byte on (to the end of the
field); Val will extract the first numeric substring of that string.
That is, for PartNumber DR1241, SortKey will be an integer 1241; for
PartNumber DR3125A it will be 3125; for PartNumber DR0010 it will be
10.

John W. Vinson[MVP]

 

[Dustin]

Thanks John, it worked. But now I have another problem; I have to import the
Part No's from Excel so I don't have to retype them. So my problem is that
they are coming over like this Table 1R1011 and Table 12R12210. They are
sorting by the last numbers fine, but the Table No's are sorting in text
format still.

How can I change the expression so that it will sort by the table no first
then by the last no's?

I really appreciate your respose.
Thanks

This worked but when I ran the query it doesn't show show me the DR in front
of the number and it doesn't show me DR1241. It showed me only numbers that
ended with 0. What do I now?
And I have just noticed that I have letters on the end of some part no's as
well. How does that figure into the expression?

You need to include both the actual field (for display) *and* the
calculated field (just for sorting). Try

SortKey: Val(Mid([PartNumber], 3))

Mid() will return the string from the third byte on (to the end of the
field); Val will extract the first numeric substring of that string.
That is, for PartNumber DR1241, SortKey will be an integer 1241; for
PartNumber DR3125A it will be 3125; for PartNumber DR0010 it will be
10.

John W. Vinson[MVP]

 

[John Vinson]

Thanks John, it worked. But now I have another problem; I have to import the
Part No's from Excel so I don't have to retype them. So my problem is that
they are coming over like this Table 1R1011 and Table 12R12210. They are
sorting by the last numbers fine, but the Table No's are sorting in text
format still.

How can I change the expression so that it will sort by the table no first
then by the last no's?

How are the Table No's stored in your table, or query? As text or as a
number? What's actually IN the field or fields?

John W. Vinson[MVP]

 

[Homer J Simpson]

Thanks John, it worked. But now I have another problem; I have to import
the
Part No's from Excel so I don't have to retype them. So my problem is that
they are coming over like this Table 1R1011 and Table 12R12210. They
are
sorting by the last numbers fine, but the Table No's are sorting in text
format still.

How can I change the expression so that it will sort by the table no first
then by the last no's?

Any time you have a situation like this it is a big red flag that you need
to handle the data differently. You could create Excel functions to split
the data before you import it OR you could create queries to do it in Access
after importing.

Either way, you need a field to store the DR part and a separate field to
store the numeric part. Now you can sort first by the DR part and then by
the numeric part or as desired. See how much better that is? Now you have
control and flexibility.