Changing sort to Alphabetic

G

Guest

I need to sort a table on one field into ascending order but by capital
alphabetic not numeric order.
I.E.
Methomyl
4-Methoxy-1,3-benzene-diamine
1-Methoxy-2-propanol
p-Methoxyaniline
Methoxychlor
2,2-bis(p-Methoxyphenyl)-1,1,1-trichloroethane

Note the 4- or p- at the beginning is ignored and this can be more than two
characters
 
G

Guest

Close but no cigar. If the first dash "-" was always the decider, something
like the following would work:

SELECT TblSortByText.Chemical
FROM TblSortByText
ORDER BY Mid([Chemical],InStr([Chemical],"-")+1);

However you want to bis removed from consideration to. I'll have to think
about this a little longer.
 
J

John Spencer

Are you saying you want to sort based on the first upper case letter
followed by the rest of the characters?
Or is there a different meaning to how you want to sort.

I would think this would require a custom function to build the sort word
and then sort by that
UNTESTED VBA FUNCTION FOLLOWS

In the query add a field
sBuildSortWord ([Your Field]) and sort by this field.

Function sBuildSortWord(strIn)
Dim i as Long, iAsc as Long
Dim strReturn as string

If Len(strIn & vbNullString) = 0 Then
sBuildSortWord = strIn
Else
'Identify first upper case letter and build sort word
For i = 1 to Len(strIn)
iAsc= Asc(Mid(strIn,i))
If iAsc >= 65 and iAsc<=90 then
' Truncate at first ucase letter (and add word at end to handle
ties)
strReturn= Mid(strIN,i) & strIn
Exit For
End if
Next i

'No upper case letter so sort word should be at end of sort.
If strReturn = vbNullString then
strReturn = "zzzzzzzzzzz" & strIn
End if
sBuildSortWord = strReturn

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

Definately no cigar.

The sort is to be on the first Capital Letter not the first dash.

Jerry Whittle said:
Close but no cigar. If the first dash "-" was always the decider, something
like the following would work:

SELECT TblSortByText.Chemical
FROM TblSortByText
ORDER BY Mid([Chemical],InStr([Chemical],"-")+1);

However you want to bis removed from consideration to. I'll have to think
about this a little longer.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


T said:
I need to sort a table on one field into ascending order but by capital
alphabetic not numeric order.
I.E.
Methomyl
4-Methoxy-1,3-benzene-diamine
1-Methoxy-2-propanol
p-Methoxyaniline
Methoxychlor
2,2-bis(p-Methoxyphenyl)-1,1,1-trichloroethane

Note the 4- or p- at the beginning is ignored and this can be more than two
characters
Click to expand...
Click to expand...
 
G

Guest

This is far closer.

This would need to be checked but it is very close to what I am looking for.
To clarify better, this is part of the list in order.
Methomyl
4-Methoxy-1,3-benzene-diamine
p-Methoxyaniline
2,2-bis(p-Methoxyphenyl)-1,1,1-trichloroethane
Methyl acetate
There will need to be check for names that do not have a capital letter in
it and these will need to be sorted at the end of the list.

John Spencer said:
Are you saying you want to sort based on the first upper case letter
followed by the rest of the characters?
Or is there a different meaning to how you want to sort.

I would think this would require a custom function to build the sort word
and then sort by that
UNTESTED VBA FUNCTION FOLLOWS

In the query add a field
sBuildSortWord ([Your Field]) and sort by this field.

Function sBuildSortWord(strIn)
Dim i as Long, iAsc as Long
Dim strReturn as string

If Len(strIn & vbNullString) = 0 Then
sBuildSortWord = strIn
Else
'Identify first upper case letter and build sort word
For i = 1 to Len(strIn)
iAsc= Asc(Mid(strIn,i))
If iAsc >= 65 and iAsc<=90 then
' Truncate at first ucase letter (and add word at end to handle
ties)
strReturn= Mid(strIN,i) & strIn
Exit For
End if
Next i

'No upper case letter so sort word should be at end of sort.
If strReturn = vbNullString then
strReturn = "zzzzzzzzzzz" & strIn
End if
sBuildSortWord = strReturn

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

T said:
I need to sort a table on one field into ascending order but by capital
alphabetic not numeric order.
I.E.
Methomyl
4-Methoxy-1,3-benzene-diamine
1-Methoxy-2-propanol
p-Methoxyaniline
Methoxychlor
2,2-bis(p-Methoxyphenyl)-1,1,1-trichloroethane

Note the 4- or p- at the beginning is ignored and this can be more than
two
characters
Click to expand...
Click to expand...
 
J

John Spencer

There was a syntax error in my code. Here is a revised version.
Try it and see if it gives you the desired results.

If not, perhaps you would care to tell us what is wrong with the results you
get. It worked for me on the limited set of data you gave me.

Public Function sBuildSortWord(strIn)
Dim i As Long, iAsc As Long
Dim strReturn As String

If Len(strIn & vbNullString) = 0 Then
sBuildSortWord = strIn
Else
' Identify first upper case letter
' and build sort word
For i = 1 To Len(strIn)

iAsc = Asc(Mid(strIn, i))
If iAsc >= 65 And iAsc <= 90 Then
' Truncate at first ucase letter (
' and add word at end to handle ties)
strReturn = Mid(strIn, i) & strIn
Exit For
End If

Next i

' No upper case letter so sort word
' should be at end of sort.
If strReturn = vbNullString Then
strReturn = "zzzzzzzzzzz" & strIn
End If

sBuildSortWord = strReturn
End If

End Function



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

T said:
This is far closer.

This would need to be checked but it is very close to what I am looking
for.
To clarify better, this is part of the list in order.
Methomyl
4-Methoxy-1,3-benzene-diamine
p-Methoxyaniline
2,2-bis(p-Methoxyphenyl)-1,1,1-trichloroethane
Methyl acetate
There will need to be check for names that do not have a capital letter in
it and these will need to be sorted at the end of the list.

John Spencer said:
Are you saying you want to sort based on the first upper case letter
followed by the rest of the characters?
Or is there a different meaning to how you want to sort.

I would think this would require a custom function to build the sort word
and then sort by that
UNTESTED VBA FUNCTION FOLLOWS

In the query add a field
sBuildSortWord ([Your Field]) and sort by this field.

Function sBuildSortWord(strIn)
Dim i as Long, iAsc as Long
Dim strReturn as string

If Len(strIn & vbNullString) = 0 Then
sBuildSortWord = strIn
Else
'Identify first upper case letter and build sort word
For i = 1 to Len(strIn)
iAsc= Asc(Mid(strIn,i))
If iAsc >= 65 and iAsc<=90 then
' Truncate at first ucase letter (and add word at end to
handle
ties)
strReturn= Mid(strIN,i) & strIn
Exit For
End if
Next i

'No upper case letter so sort word should be at end of sort.
If strReturn = vbNullString then
strReturn = "zzzzzzzzzzz" & strIn
End if
sBuildSortWord = strReturn

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

T said:
I need to sort a table on one field into ascending order but by capital
alphabetic not numeric order.
I.E.
Methomyl
4-Methoxy-1,3-benzene-diamine
1-Methoxy-2-propanol
p-Methoxyaniline
Methoxychlor
2,2-bis(p-Methoxyphenyl)-1,1,1-trichloroethane

Note the 4- or p- at the beginning is ignored and this can be more than
two
characters
Click to expand...
Click to expand...
Click to expand...
 
Top