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?

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


"Dustin" wrote:

> 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?

Create a field in the order section of the query with

Order By Mid([FieldName],3)

--
\\// Live Long and Prosper \\//
BS"D


"Dustin" wrote:

> 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?

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

"Kevin B" wrote:

> 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
>
>
> "Dustin" wrote:
>
> > 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?

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


"Dustin" wrote:

> 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
>
> "Kevin B" wrote:
>
> > 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
> >
> >
> > "Dustin" wrote:
> >
> > > 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?

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

"Kevin B" wrote:

> 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
>
>
> "Dustin" wrote:
>
> > 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
> >
> > "Kevin B" wrote:
> >
> > > 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
> > >
> > >
> > > "Dustin" wrote:
> > >
> > > > 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?

On Thu, 2 Mar 2006 15:21:27 -0800, Dustin
<(E-Mail Removed)> wrote:

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

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



"John Vinson" wrote:

> On Thu, 2 Mar 2006 15:21:27 -0800, Dustin
> <(E-Mail Removed)> wrote:
>
> >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]
>

On Fri, 3 Mar 2006 06:56:27 -0800, Dustin
<(E-Mail Removed)> wrote:

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

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

"John Vinson" wrote:

> On Fri, 3 Mar 2006 06:56:27 -0800, Dustin
> <(E-Mail Removed)> wrote:
>
> >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]
>