sorting on alfanumerical values

[romain]

I have a database with a code field which contains alfanumerical values like:
IB1, IB2, IB3...IB180, and so on.
When I sort the usual way A-->Z, I obtain:
IB1, IB10, IB100, IB101...IB109, IB11, IB110, IB111

How can I sort nicely to obtain IB1, IB2, IB3...

To complicate things, there are alfanumerical values with a different number of letters before the figures: like F1, F2... and SER1, SER2 and so on.

Is there any plugin to nicely sort those codes?

thanks in advance

Romain

 

[antomack]

If you add the following function to your database and then add an extra field to your query as RmLeadChar([YourFieldName]) and set the order on this new field you should get the result you want regardless of how many leading characters are on the field. You can leave the new field hidden if you want.

Public Function RmLeadChar(strCheck As String)
  Dim i As Integer

  If Not IsNull(strCheck) And strCheck <> "" Then
    For i = 1 To Len(strCheck)
      If IsNumeric(Mid(strCheck, i, 1)) Then
        Exit For
      End If
    Next i
    strCheck = Mid(strCheck, i)
  End If
  
  RmLeadChar = strCheck
  
End Function

 

[antomack]

Sorry had jumped the gun a bit there. The above function will only work if the lead characters are the same for all fields. To cater for differing lead characters you will also need to add the function below and another field to the query as GetLeadChar([YourFieldName]). This field will need to go before the RmLeadChar and it's order should also be set to whichever order you require. The combination of the two will give you what you want regardless of how many differing groups of lead characters you have.

Public Function GetLeadChar(strCheck As String)
  Dim i As Integer

  If Not IsNull(strCheck) And strCheck <> "" Then
    For i = 1 To Len(strCheck)
      If IsNumeric(Mid(strCheck, i, 1)) Then
        Exit For
      End If
    Next i
    strCheck = left(strCheck, i - 1)
  End If
  
  GetLeadChar = strCheck
  
End Function

 

[Bat17]

I think that as there is a need to sort on the numeric portion as numbers rather than text then you will have to pad the numeric portion to a standard lenth and use that for sorting

Public Function PackChar(strCheck)
  Dim i As Integer
  If strCheck & "" <> "" Then
    For i = 1 To Len(strCheck)
      If IsNumeric(Mid(strCheck, i, 1)) Then
        Exit For
      End If
    Next i
  End If
PackChar = Left(strCheck, i - 1) & Right("0000000000" & Mid(strCheck, i), 10)
End Function

HTH

Peter