ignoring numbers and brackets

[Anna Busby]

I have a question that relates to setting up a database for a laboratory
chemicals list. I've managed to import all the values from an Excel
spreadsheet but when I go to sort them I find that the program takes me too
literally as some of the chemical names start with a bracket or number. For
example:
1,3 Propanedithiol
which should come under P is near the top.

Can anyone help me please?

 

[workATaccess]

How about setting up a separate field for the pre-fixes (the 1,3 or brackets)?

 

[workATaccess]

Set up the separate field in the access table as "prefixes", the have the
"main chemical name" in its own field. This way when you export you will be
able to sort using the the "main chemical name" field.
Good luck.

 

[Kerry]

Hi Anna,

You could create a function that ignores what you want to ignore, then
sort by that. For example:

Public Function IgnoreNumbersBrackets(InititalValue) As Variant
Dim intPos As Integer
Dim strTemp As String
Let IgnoreNumbersBrackets = Null
If IsNull(InititalValue) Then Exit Function
If Len(InititalValue) = 0 Then
Let IgnoreNumbersBrackets = InititalValue
End If
Let strTemp = InititalValue
CheckFirstChar:
Select Case Left(strTemp, 1)
Case "0" To "9", "[", "]", " ",","
Let strTemp = Mid(strTemp, 2)
GoTo CheckFirstChar
End Select
Let IgnoreNumbersBrackets = strTemp
End Function

 

[Ken Sheridan]

Add the following function to a standard module:

Public Function ChemName(varChemName As Variant) As String

Dim i As Integer, n As Integer
Dim strChemName As String

If Not IsNull(varChemName) Then
strChemName = varChemName
n = Len(strChemName)
' step through name and remove characters
' until first alphabetic character is encountered
For i = 1 To n
Select Case Asc(Mid$(strChemName, 1, 1))
Case 65 To 90 ' upper case letter
Exit For
Case 97 To 122 ' lower case letter
Exit For
Case Else
' remove character
strChemName = Mid$(strChemName, 2)
End Select
Next i
End If

ChemName = strChemName

End Function

You can then call it, passing the chemical names as imported into the
function as its argument, ChemName([Chemical Name])

You can use it in whatever way is most appropriate, e.g.

1. Permanently remove the leading non-alphabetic characters by means of an
update query:

UPDATE [YourTable]
SET [Chemical Name] = ChemName([Chemical Name]);

2. View the full name as imported but sort by the name trimmed of the
non-alphabetic characters:

SELECT [Chemical Name]
FROM [YourTable]
ORDER BY ChemName([Chemical Name]);

3. Keep the full name as imported in the table but view it and sort it
trimmed of the non-alphabetic characters:

SELECT ChemName([Chemical Name]) AS [Trimmed Name]
FROM [YourTable]
ORDER BY ChemName([Chemical Name]);

Ken Sheridan
Stafford, England