need help sorting text by trailing Numbers Value
in [Reports]
|
From: Barry A&P on 4 Mar 2010 14:18 I am looking for some help sorting a text field (PartNumbers) alphabetically but correctly depending on the value of the ending few charachters if theyre numbers.. Here is some sample data an960pd10 an960pd300 an960pd6 i would like it sorted like this an960pd6 an960pd10 an960pd300 any ideas? Thanks Barry
From: Duane Hookom on 4 Mar 2010 14:44 I looks like your values have a fixed length to the left of the numbers. If this is true, you can use the following in the Sorting and Grouping dialog expression: =Left([PartNumbers],7) =Val(Mid([PartNumbers],8)) -- Duane Hookom Microsoft Access MVP "Barry A&P" wrote: > I am looking for some help sorting a text field (PartNumbers) alphabetically > but correctly depending on the value of the ending few charachters if theyre > numbers.. > > Here is some sample data > > an960pd10 > an960pd300 > an960pd6 > > i would like it sorted like this > an960pd6 > an960pd10 > an960pd300 > > any ideas? > Thanks > Barry
From: Barry A&P on 5 Mar 2010 20:24 Duane Thanks for looking at my post.. Im sorry but i gave a pretty poor example of my sample data as they are not fixed length PartNumbers and i forgot some have a trailing alpha. i tried out your suggestion in a query and although my results (because of the non fixed length) are a little goofy you have me heading in the right direction. Unless maybe my data is too complex and needs a little VBA code?? here is what a hardware p/n consists of an960pd416L an960 is the style pd is the material 416 is the size L is a revision attribute Here is another sample an960-416 an960 is the style - is added in because material is not specified 416 is the size and there is no revision additional samples ms28778-1-034A ms28778-1-066A ms28778-1-4 so my sort needs to (starting from the end of the partNumber) first split off a trailing alpha charachter (if Present) Next get value of all numeric charachters to the next Alpha (hopefully this includes - /#) Now sort first by whats left of the leading part numbers then by the value of the extracted numerical section and lastly by the trailing alpha if present here is the query where i tried your earlier suggestion SELECT T_PartNumbers.PartNumber FROM T_PartNumbers ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8)); I have been trying to add a sort value field but ive only covered a few hundred records of a few thousand. Thanks for any help Barry "Duane Hookom" wrote: > I looks like your values have a fixed length to the left of the numbers. If > this is true, you can use the following in the Sorting and Grouping dialog > expression: > > =Left([PartNumbers],7) > =Val(Mid([PartNumbers],8)) > > -- > Duane Hookom > Microsoft Access MVP > > > "Barry A&P" wrote: > > > I am looking for some help sorting a text field (PartNumbers) alphabetically > > but correctly depending on the value of the ending few charachters if theyre > > numbers.. > > > > Here is some sample data > > > > an960pd10 > > an960pd300 > > an960pd6 > > > > i would like it sorted like this > > an960pd6 > > an960pd10 > > an960pd300 > > > > any ideas? > > Thanks > > Barry
From: Duane Hookom on 5 Mar 2010 23:50 Please provide the "sort" expressions from multiple actual part numbers. If I were you, I would immediately change the structure so a field stores a single value/item. -- Duane Hookom MS Access MVP "Barry A&P" <BarryAP(a)discussions.microsoft.com> wrote in message news:DC1729BB-D3E8-4504-B344-07989B244352(a)microsoft.com... > Duane > > Thanks for looking at my post.. Im sorry but i gave a pretty poor example > of my sample data as they are not fixed length PartNumbers and i forgot > some > have a trailing alpha. > > i tried out your suggestion in a query and although my results (because of > the non fixed length) are a little goofy you have me heading in the right > direction. > > Unless maybe my data is too complex and needs a little VBA code?? > > here is what a hardware p/n consists of > an960pd416L > an960 is the style > pd is the material > 416 is the size > L is a revision attribute > > Here is another sample > an960-416 > an960 is the style > - is added in because material is not specified > 416 is the size > and there is no revision > > additional samples > ms28778-1-034A > ms28778-1-066A > ms28778-1-4 > > so my sort needs to (starting from the end of the partNumber) first split > off a trailing alpha charachter (if Present) > > Next get value of all numeric charachters to the next Alpha (hopefully > this > includes - /#) > > Now sort first by whats left of the leading part numbers > then by the value of the extracted numerical section > and lastly by the trailing alpha if present > > here is the query where i tried your earlier suggestion > > SELECT T_PartNumbers.PartNumber > FROM T_PartNumbers > ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8)); > > I have been trying to add a sort value field but ive only covered a few > hundred records of a few thousand. > > Thanks for any help > Barry > > "Duane Hookom" wrote: > >> I looks like your values have a fixed length to the left of the numbers. >> If >> this is true, you can use the following in the Sorting and Grouping >> dialog >> expression: >> >> =Left([PartNumbers],7) >> =Val(Mid([PartNumbers],8)) >> >> -- >> Duane Hookom >> Microsoft Access MVP >> >> >> "Barry A&P" wrote: >> >> > I am looking for some help sorting a text field (PartNumbers) >> > alphabetically >> > but correctly depending on the value of the ending few charachters if >> > theyre >> > numbers.. >> > >> > Here is some sample data >> > >> > an960pd10 >> > an960pd300 >> > an960pd6 >> > >> > i would like it sorted like this >> > an960pd6 >> > an960pd10 >> > an960pd300 >> > >> > any ideas? >> > Thanks >> > Barry
From: Barry A&P on 8 Mar 2010 12:16 Duane I was looking at my data and i am beginning to fear the sort is too complex, or atleast too many variables to look at.. The field structure im afraid is correct. as they are manufacturers part numbers and i am trying to sort the nuts bolts and screws by physical size instead of alphabetically by partnumber. I would like to refine my question to these.. if my PartNumber ends with a single Alphabetical charachter how can i get that value and move it to another field.. so i would remove the "R" from ms234-20R but would not touch ms35266SS because there are 2 trailing ahpha charachters Then in the next step how could i move all of the trailing Numerical charachters to a new field? so i could then remove the "20" from ms234-20 or 2445 from M83248-1-A2445 I think if i could somehow create a sub that could count the number of charachters in the part number then use if right(partnumber,1) = alpha and right(partnumber,2,1) <> alpha then new field = right(partnumber,1) and do the same for numbers but keep all the numbers till i get <> Numberchar but not sure how to test if its a number or a letter or if thats even possible if its all in a text field.. Thanks Barry "Duane Hookom" wrote: > Please provide the "sort" expressions from multiple actual part numbers. > If I were you, I would immediately change the structure so a field stores a > single value/item. > > > -- > Duane Hookom > MS Access MVP > > > "Barry A&P" <BarryAP(a)discussions.microsoft.com> wrote in message > news:DC1729BB-D3E8-4504-B344-07989B244352(a)microsoft.com... > > Duane > > > > Thanks for looking at my post.. Im sorry but i gave a pretty poor example > > of my sample data as they are not fixed length PartNumbers and i forgot > > some > > have a trailing alpha. > > > > i tried out your suggestion in a query and although my results (because of > > the non fixed length) are a little goofy you have me heading in the right > > direction. > > > > Unless maybe my data is too complex and needs a little VBA code?? > > > > here is what a hardware p/n consists of > > an960pd416L > > an960 is the style > > pd is the material > > 416 is the size > > L is a revision attribute > > > > Here is another sample > > an960-416 > > an960 is the style > > - is added in because material is not specified > > 416 is the size > > and there is no revision > > > > additional samples > > ms28778-1-034A > > ms28778-1-066A > > ms28778-1-4 > > > > so my sort needs to (starting from the end of the partNumber) first split > > off a trailing alpha charachter (if Present) > > > > Next get value of all numeric charachters to the next Alpha (hopefully > > this > > includes - /#) > > > > Now sort first by whats left of the leading part numbers > > then by the value of the extracted numerical section > > and lastly by the trailing alpha if present > > > > here is the query where i tried your earlier suggestion > > > > SELECT T_PartNumbers.PartNumber > > FROM T_PartNumbers > > ORDER BY Left([PartNumbers],7), Val(Mid([PartNumbers],8)); > > > > I have been trying to add a sort value field but ive only covered a few > > hundred records of a few thousand. > > > > Thanks for any help > > Barry > > > > "Duane Hookom" wrote: > > > >> I looks like your values have a fixed length to the left of the numbers. > >> If > >> this is true, you can use the following in the Sorting and Grouping > >> dialog > >> expression: > >> > >> =Left([PartNumbers],7) > >> =Val(Mid([PartNumbers],8)) > >> > >> -- > >> Duane Hookom > >> Microsoft Access MVP > >> > >> > >> "Barry A&P" wrote: > >> > >> > I am looking for some help sorting a text field (PartNumbers) > >> > alphabetically > >> > but correctly depending on the value of the ending few charachters if > >> > theyre > >> > numbers.. > >> > > >> > Here is some sample data > >> > > >> > an960pd10 > >> > an960pd300 > >> > an960pd6 > >> > > >> > i would like it sorted like this > >> > an960pd6 > >> > an960pd10 > >> > an960pd300 > >> > > >> > any ideas? > >> > Thanks > >> > Barry >
|
Next
|
Last
Pages: 1 2 Prev: charts from Data just in two different reports Next: Code to read email from Outlook |