Function Sort text identifiers with SQL Assending (1 Viewer)

[Rx_]

Problem: The Access "Accending Order" can't manage this format properly

Customer 10-05H
Customer 1-02H
Customer 11-07H
Customer 12-08H
Customer 13-17H
Customer 14-1807H
Customer 15-19H
Customer 16-20H
Customer 17-21H
Customer 18-22H
Customer 19-23H
Customer 20-24H
Customer 2-03H
Customer 21-26H
Customer 22-27H
Customer 23-33H
Customer 24-31H
Customer 25-31H
Customer 26-33H
Customer 27-34H
Customer 28-35H
Customer 30-26H
Customer 3-04H
Customer 31-02H

This format is easier to read and queries using Assending put it back into human readable form. note the 10 no longer preceeds the 1

Customer 001-02H
Customer 002-03H
Customer 003-04H
Customer 004-09H
Customer 005-10H
Customer 006-12H
Customer 007-13H
Customer 008-14H
Customer 009-15H
Customer 010-05H
Customer 011-07H
Customer 012-08H
Customer 013-17H
Customer 014-1807H
Customer 015-19H
Customer 016-20H
Customer 017-21H
Customer 018-22H
Customer 019-23H
Customer 020-24H
Customer 021-26H
Customer 022-27H
Customer 023-33H
Customer 024-31H
Customer 025-31H
Customer 026-33H
Customer 027-34H
Customer 028-35H
Customer 030-26H
Customer 031-02H

Your special characters may vary.
Find numbers based on formatted characters (i.e. the dash) and replace them
with character zeros. The Assending sort order will manage the rest

Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : Customer_ID_Sorted
' Author    : RxMiller
' Date      : 3/22/2010
' Purpose   : Sort customername, look at numbers before and after dash and then sort numerically using the Assending
'---------------------------------------------------------------------------------------
'
Public Function [B]Customer_Name_Sorted[/B](CustomerName) As String
10    On Error Resume Next
          Dim sResult             As String
          Dim CustomerNameText        As String
          Dim SearchChar          As String
          Dim MyDashPos           As Integer
          Dim StringLength        As Integer
          Dim PreceedingSpace     As Integer
          Dim SpaceChar           As Integer
          Dim NumbersWithDash     As String
          Dim LeftNumber          As String
          Dim RightNumber         As String
          Dim intRightNumber      As Integer
          Dim intLeftNumber       As Integer
          Dim CustomerNameOnly        As String
 
20        CustomerNameText = CustomerName
30        StringLength = Len(CustomerNameText)
40        SearchChar = "-"                                    ' find dashes (your characters may vary
          'SpaceChar = " "                                    ' need to find ascii  space
50        MyDashPos = InStr(1, CustomerNameText, SearchChar, vbTextCompare)       ' position of "-" in entire string
60        If MyDashPos > 0 Then
70                PreceedingSpace = InStr(MyDashPos - 4, CustomerNameText, " ", vbTextCompare) ' position of space before "-"
80                CustomerNameOnly = Left(CustomerNameText, StringLength - (StringLength - PreceedingSpace))
90                NumbersWithDash = Right(CustomerName, StringLength - (PreceedingSpace))
100               MyDashPos = InStr(1, NumbersWithDash, SearchChar, vbTextCompare)    ' position of "-" in shortened number only
110               LeftNumber = Left(NumbersWithDash, MyDashPos - 1)
120               RightNumber = Right(NumbersWithDash, Len(NumbersWithDash) - MyDashPos)
                  ' Convert numbers to formatted text
 
130               If IsNumeric(LeftNumber) Then
140                   intLeftNumber = CInt(LeftNumber)
150                   LeftNumber = Format(intLeftNumber, "000")
160               End If
170               If IsNumeric(RightNumber) Then
180                   intRightNumber = CInt(RightNumber)
190                   RightNumber = Format(intRightNumber, "000")
200               End If
210           sResult = CustomerNameOnly & LeftNumber & "-" & RightNumber
220       Else
230           sResult = CustomerName
240       End If
          ' return value                  
250               Customer_Name_Sorted = sResult
' see Customer_Name_Sorted function used in SQL 
' Example: strSQL = "SELECT DISTINCT Customers_Sorted.Area, [B]Customer_Name_Sorted([Customers_Sorted].[Customer_Name])[/B] AS [Customer Name], Customers_Sorted.Status1 AS [Customer Status], " & _
'  .
'  .
'  . 
' "ORDER BY Customers_Sorted.Area, [B]Customer_Name_Sorted([Customers_Sorted].[Customer_Name]),[/B] [08_Team_Permit_Final_Excel_Local].[State Submit] DESC , 
 
' then Order BY uses function
' Use in list boxes, reports and other user interface objects           
 
End Function