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