ignore numbers in a sort

[Angela]

I am creating a chemical database that contains a field with chemical names
like 2,4-trifuoro... and 1,1,1-dinitro... I want to alphabetize the list to
ignore the leading numbers. How does one do a sort to ignore anything before
the first actual letter?

Many thanks to any help.

Angela Archer
Gustavus Adolphus College

 

[Rick Gittins]

You can create a query or code that would look at the ASCII value of each
character in the field to check if it is a letter.

If you need help doing this let me know.

Rick G

 

[fredg]

I am creating a chemical database that contains a field with chemical names
like 2,4-trifuoro... and 1,1,1-dinitro... I want to alphabetize the list to
ignore the leading numbers. How does one do a sort to ignore anything before
the first actual letter?

Many thanks to any help.

Angela Archer
Gustavus Adolphus College

I assume you also want to ignore the - in 2,4-trifuoro.

You will need to create query to use as the record source for your
report or form.

Add a new column to the query:
SortThis:StartOfText([FieldName])

In a new Module, copy and paste the following function:

Public Function StartOfText(FieldIn)
Dim intX As Integer
Dim intY As Integer

For intX = 1 To Len(FieldIn)
intY = Asc(Mid(FieldIn, intX, 1))
If intY > 57 Then
StartOfText = Mid(FieldIn, intX)
Exit Function
End If
Next intX
StartOfText = FieldIn

End Function
========
Add error handling as needed.

If you are using this in a form, sort the query on this [SortThis]
field.

If you are using this in a report, set the report's Sorting and
Grouping dialog to sort on this [SortThis] field.

 

[John Vinson]

I am creating a chemical database that contains a field with chemical names
like 2,4-trifuoro... and 1,1,1-dinitro... I want to alphabetize the list to
ignore the leading numbers. How does one do a sort to ignore anything before
the first actual letter?

That's going to be tricky - inefficient at best, and probably
difficult to program. How big is the table? If you're sorting CAS's
umpteen million names you're in trouble! <g>

I'd suggest adding a Sortkey field, and writing a VBA function to fill
it. I would presume that you would like 1,2-difluorobenzene to sort
before 1,3-difluorobenzene, so you don't want to totally *ignore* the
numbers; with a sortkey field containing only the alpha characters you
could sort first by it, and (for a rough cut) second by the name
itself.

Air code, untested, to extract only the letters:

Public Function JustLetters(strName As String) As String
Dim strWork As String
Dim iPos As Integer
Dim iChr As Integer
JustLetters = "" ' initialize return value
strWork = UCase(strName) ' get all text into upper case
For iPos = 1 to Len(strWork) ' loop through the name
iChr = Asc(Mid(strWork, iPos, 1)) ' get the ASCII value of each letter
If iChr >= 65 And iChr <= 90 Then ' in range A to Z?
JustLetters = JustLetters & Chr(iChr) ' append the letter
Next iPos
End Function

If you add the Sortkey field, and then run an Update query updating it
to

JustLetters([chemicalnamefield])

you can hopefully get something useful for sorting.

John W. Vinson[MVP]