need help sorting text by trailing Numbers Value
in [Reports]
|
From: Duane Hookom on 9 Mar 2010 10:21 Again "Please provide the "sort" expressions from multiple actual part numbers" How about giving us at least 10 stored values and how they should be sorted? -- Duane Hookom Microsoft Access MVP "Barry A&P" wrote: > 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 > >
From: Barry A&P on 10 Mar 2010 13:35 Duane i do not have a "sort" expressions other than ORDER BY T_PartNumbers.PartNumber; here is more sample data sorted alphabetically.. AN5-12A AN5-13A AN525-10R8 AN526-832R6 AN5-35A AN5-6A AN960-6 AN960-616 AN960-616L AN960-6L AN960-716 AN960-716L AN960-8 AN960-816 AN960-816L AN960-8L AN960-916 AN960-916L CD-10 CD-12 CD-16 CD-3 CD-4 CD-6 CD-8 CR2249-4-01 CR2249-4-10 CR2249-4-5 CR2249-5-5 CR2249-6-3 here is the same data hand sorted.. AN5-6A AN5-12A AN5-13A AN5-35A AN525-10R8 AN526-832R6 AN960-6 AN960-6L AN960-8 AN960-8L AN960-616 AN960-616L AN960-716 AN960-716L AN960-816 AN960-816L AN960-916 AN960-916L CD-3 CD-4 CD-6 CD-8 CD-10 CD-12 CD-16 CR2249-4-01 CR2249-4-5 CR2249-4-10 CR2249-5-5 CR2249-6-3 I have been playing with VBA to do this.. and i can handle a few if statements to see if there is a trailing [a-z] but what i cant figure out how to do is some kind of loop that will get the numerical section until it gets to the next [!0-9] I subsituted the Partnumber value that would m for example if right(PartNumber,1) like [0-9] then 'partnumber = CR2249-4-01 "start code to get digits until it hits the - so results would be strA = CR2249-4- and strB = 01 elseif mid(PartNumber,len(PartNumber)-1,1) like [0-9] 'for Partnumber = an960pd416L "start code to get digits until it hits the "d" so results would be StrA = an960pd and StrB = 416 else strA = partnumber I feel im so lost on this i am just making stupid comments now. Hope you can make something of this. Or shoot me down and put me out of this misery. if im nuts Thanks Barry "Duane Hookom" wrote: > Again "Please provide the "sort" expressions from multiple actual part numbers" > How about giving us at least 10 stored values and how they should be sorted? > > -- > Duane Hookom > Microsoft Access MVP > > > "Barry A&P" wrote: > > > 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 > > >
From: John Spencer on 11 Mar 2010 09:14 You can try the following VBA code and see if it works to give you a sort string you can use. It seems to work in my limited testing. Public Function fStringNumberSort(strIn) As Variant Dim strReturn As String Dim i As Long Dim strNumbers As String 'Set the number of zeros to be used for the sort string Const csZeroString As String = "00000000" If Len(Trim(strIn & vbNullString)) = 0 Then 'return null or spaces or zero length string fStringNumberSort = strIn ElseIf strIn Like "*[0-9]*" = False Then 'No numbers so we are done fStringNumberSort = strIn Else 'Handle cases where there is one of more number characters For i = 1 To Len(strIn) If IsNumeric(Mid(strIn, i, 1)) = True Then strNumbers = strNumbers & Mid(strIn, i, 1) Else 'Add the number string If Len(strNumbers) > 0 Then strReturn = strReturn & Format(strNumbers, csZeroString) strNumbers = vbNullString End If 'Add the non-number characters strReturn = strReturn & Mid(strIn, i, 1) End If Next i If Len(strNumbers) > 0 Then strReturn = strReturn & Format(strNumbers, csZeroString) strNumbers = vbNullString End If fStringNumberSort = strReturn End If End Function John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Barry A&P wrote: > Duane > i do not have a "sort" expressions other than ORDER BY > T_PartNumbers.PartNumber; > here is more sample data sorted alphabetically.. > > AN5-12A > AN5-13A > AN525-10R8 > AN526-832R6 > AN5-35A > AN5-6A > AN960-6 > AN960-616 > AN960-616L > AN960-6L > AN960-716 > AN960-716L > AN960-8 > AN960-816 > AN960-816L > AN960-8L > AN960-916 > AN960-916L > CD-10 > CD-12 > CD-16 > CD-3 > CD-4 > CD-6 > CD-8 > CR2249-4-01 > CR2249-4-10 > CR2249-4-5 > CR2249-5-5 > CR2249-6-3 > > here is the same data hand sorted.. > > AN5-6A > AN5-12A > AN5-13A > AN5-35A > AN525-10R8 > AN526-832R6 > AN960-6 > AN960-6L > AN960-8 > AN960-8L > AN960-616 > AN960-616L > AN960-716 > AN960-716L > AN960-816 > AN960-816L > AN960-916 > AN960-916L > CD-3 > CD-4 > CD-6 > CD-8 > CD-10 > CD-12 > CD-16 > CR2249-4-01 > CR2249-4-5 > CR2249-4-10 > CR2249-5-5 > CR2249-6-3 > > I have been playing with VBA to do this.. and i can handle a few if > statements to see if there is a trailing [a-z] but what i cant figure out > how to do is some kind of loop that will get the numerical section until it > gets to the next [!0-9] > I subsituted the Partnumber value that would m > for example if right(PartNumber,1) like [0-9] then 'partnumber = CR2249-4-01 > "start code to get digits until it hits the - so results would be strA = > CR2249-4- and strB = 01 > > elseif mid(PartNumber,len(PartNumber)-1,1) like [0-9] 'for Partnumber = > an960pd416L > "start code to get digits until it hits the "d" so results would be StrA = > an960pd and StrB = 416 > > else > strA = partnumber > > I feel im so lost on this i am just making stupid comments now. > Hope you can make something of this. > Or shoot me down and put me out of this misery. if im nuts > > Thanks > Barry > > "Duane Hookom" wrote: > > > >> Again "Please provide the "sort" expressions from multiple actual part numbers" >> How about giving us at least 10 stored values and how they should be sorted? >> >> -- >> Duane Hookom >> Microsoft Access MVP >> >> >> "Barry A&P" wrote: >> >>> 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
From: Barry A&P on 11 Mar 2010 18:25 john Absolutely amazing it works great. and handles more data than i expected. I was sure i would still have to work with the data after it was sorted.. SELECT T_PartNumbers.PartNumber, fStringNumberSort([partNumber]) AS SortValue FROM T_PartNumbers ORDER BY fStringNumberSort([partNumber]); I do have a few oddballs that i can deffinately live with.. the data that begins with alphabetical charachters is perfect but the numbers that start with nomerical charachters arent quite as expected. if there is an easy fix it would be even better... here is a big chunk of sample data notice how it revisits the first set of numbers would it be possible to negate the first set numerical string if the code comes up with more than one numerical string in its results?? PartNumber Sort Value 1-300-686-03 00000001-00000300-00000686-00000003 1-1282-65 00000001-00001282-00000065 1-225663-5 00000001-00225663-00000005 35C4908 00000035C00004908 35C4909 00000035C00004909 570-074-315-3 00000570-00000074-00000315-00000003 570-076-002-1 00000570-00000076-00000002-00000001 574-074-272-1 00000574-00000074-00000272-00000001 696-41960 00000696-00041960 839-00154 00000839-00000154 950-315 00000950-00000315 1001-4000-01 00001001-00004000-00000001 1086 00001086 1151 00001151 1190K37 00001190K00000037 1204K12 00001204K00000012 1220-2410-2 00001220-00002410-00000002 1242T431 00001242T00000431 1266T2 00001266T00000002 1268T3 00001268T00000003 1275K33 00001275K00000033 1277K24 00001277K00000024 1277K34 00001277K00000034 Thank you very much for your suggestion i can deffinately get where i am going with this.. Barry "John Spencer" wrote: > You can try the following VBA code and see if it works to give you a sort > string you can use. It seems to work in my limited testing. > > Public Function fStringNumberSort(strIn) As Variant > Dim strReturn As String > Dim i As Long > Dim strNumbers As String > 'Set the number of zeros to be used for the sort string > Const csZeroString As String = "00000000" > > If Len(Trim(strIn & vbNullString)) = 0 Then > 'return null or spaces or zero length string > fStringNumberSort = strIn > > ElseIf strIn Like "*[0-9]*" = False Then > 'No numbers so we are done > fStringNumberSort = strIn > > Else 'Handle cases where there is one of more number characters > For i = 1 To Len(strIn) > If IsNumeric(Mid(strIn, i, 1)) = True Then > strNumbers = strNumbers & Mid(strIn, i, 1) > > Else > 'Add the number string > If Len(strNumbers) > 0 Then > strReturn = strReturn & Format(strNumbers, csZeroString) > strNumbers = vbNullString > End If > > 'Add the non-number characters > strReturn = strReturn & Mid(strIn, i, 1) > > End If > Next i > > If Len(strNumbers) > 0 Then > strReturn = strReturn & Format(strNumbers, csZeroString) > strNumbers = vbNullString > End If > > fStringNumberSort = strReturn > End If > End Function > > > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > Barry A&P wrote: > > Duane > > i do not have a "sort" expressions other than ORDER BY > > T_PartNumbers.PartNumber; > > here is more sample data sorted alphabetically.. > > > > AN5-12A > > AN5-13A > > AN525-10R8 > > AN526-832R6 > > AN5-35A > > AN5-6A > > AN960-6 > > AN960-616 > > AN960-616L > > AN960-6L > > AN960-716 > > AN960-716L > > AN960-8 > > AN960-816 > > AN960-816L > > AN960-8L > > AN960-916 > > AN960-916L > > CD-10 > > CD-12 > > CD-16 > > CD-3 > > CD-4 > > CD-6 > > CD-8 > > CR2249-4-01 > > CR2249-4-10 > > CR2249-4-5 > > CR2249-5-5 > > CR2249-6-3 > > > > here is the same data hand sorted.. > > > > AN5-6A > > AN5-12A > > AN5-13A > > AN5-35A > > AN525-10R8 > > AN526-832R6 > > AN960-6 > > AN960-6L > > AN960-8 > > AN960-8L > > AN960-616 > > AN960-616L > > AN960-716 > > AN960-716L > > AN960-816 > > AN960-816L > > AN960-916 > > AN960-916L > > CD-3 > > CD-4 > > CD-6 > > CD-8 > > CD-10 > > CD-12 > > CD-16 > > CR2249-4-01 > > CR2249-4-5 > > CR2249-4-10 > > CR2249-5-5 > > CR2249-6-3 > > > > I have been playing with VBA to do this.. and i can handle a few if > > statements to see if there is a trailing [a-z] but what i cant figure out > > how to do is some kind of loop that will get the numerical section until it > > gets to the next [!0-9] > > I subsituted the Partnumber value that would m > > for example if right(PartNumber,1) like [0-9] then 'partnumber = CR2249-4-01 > > "start code to get digits until it hits the - so results would be strA = > > CR2249-4- and strB = 01 > > > > elseif mid(PartNumber,len(PartNumber)-1,1) like [0-9] 'for Partnumber = > > an960pd416L > > "start code to get digits until it hits the "d" so results would be StrA = > > an960pd and StrB = 416 > > > > else > > strA = partnumber > > > > I feel im so lost on this i am just making stupid comments now. > > Hope you can make something of this. > > Or shoot me down and put me out of this misery. if im nuts > > > > Thanks > > Barry > > > > "Duane Hookom" wrote: > > > > > > > >> Again "Please provide the "sort" expressions from multiple actual part numbers" > >> How about giving us at least 10 stored values and how they should be sorted? > >> > >> -- > >> Duane Hookom > >> Microsoft Access MVP > >> > >> > >> "Barry A&P" wrote: > >> > >>> 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
From: John Spencer on 11 Mar 2010 18:50 This may not be what you want, but here is an attempt at what I understand you want. If you want the first number string to not be formatted no matter what then you will need to add a variable to track if the number string is the first one. And increment the variable every time you add a number string to the return string. Public Function fStringNumberSort(strIn) As Variant Dim strReturn As String Dim i As Long Dim strNumbers As String 'Set the number of zeros to be used for the sort string Const csZeroString As String = "00000000" If Len(Trim(strIn & vbNullString)) = 0 Then 'return null or spaces or zero length string fStringNumberSort = strIn ElseIf strIn Like "*[0-9]*" = False Then 'No numbers so we are done fStringNumberSort = strIn Else 'Handle cases where there is one of more number characters For i = 1 To Len(strIn) If IsNumeric(Mid(strIn, i, 1)) = True Then strNumbers = strNumbers & Mid(strIn, i, 1) Else 'Add the number string If Len(strNumbers) > 0 Then '================ Modification to Not format number if it is the first 'thing to be added to the string '===================================================================== If Len(strReturn) > 0 then strReturn = strReturn & Format(strNumbers, csZeroString) Else strReturn = strReturn & strNumbers End IF strNumbers = vbNullString End If 'Add the non-number characters strReturn = strReturn & Mid(strIn, i, 1) End If Next i If Len(strNumbers) > 0 Then strReturn = strReturn & Format(strNumbers, csZeroString) strNumbers = vbNullString End If fStringNumberSort = strReturn End If End Function John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County
First
|
Prev
|
Pages: 1 2 Prev: charts from Data just in two different reports Next: Code to read email from Outlook |