Access strings and sort problems. need pad-like function? 1

[kdre]

Help!

I have an alphanumeric, 10-character text field defined. It contains bond numbers which may be anywhere from 1 to 10 characters in length.

The problem is the sort order. If I have bond numbers A1,
A2, A11,A32, and A200 I would expect them to sort in this fashion.

However, Access sorts in the following order:
A1, A11, A2, A200, A32, which is not what I want.

In Oracle, there is a pad function, which would allow me to pad with blanks all unused bytes in the 10-character field. So, in Access or VBA, is there a way to pad these or otherwise get be stored as a 10-character field so these would sort in the expectded order?

Many, many thanks!

 

[RickSpr]

You can use a VBA expression to pad with spaces, but that wouldn't get you anywhere. The problem is that you're expecting to compare the numeric portions numerically, but VBA always compares text fields alphabetically. In effect, VBA is already padding on the right with spaces when it does the comparison. Using "b" to represent a space, VBA is comparing "A11bbbbbbb" with "A2bbbbbbbb", and saying A11 is lower.

What you need is to pad the numeric portion only with zeros on the left, and then you can sort on the whole string. How you do this depends on whether you're specifying the sorting for a query, a report, in code, or whatever. The basic expression on which you sort can be written as:
Left$(fld, 1) & Format(Val(Mid$(fld, 2)), "000000000&quot
This creates a string consisting of the leftmost character followed by a 9-digit number with leading zeros. A number padded with leading zeros will give the same results sorted either as text or numerically.

BTW, I'm assuming all bond number consist of a letter followed by a number. Rick Sprague

 

[kdre]

Thanks, Rick. I was afraid I'd have to do something like that.

Bond numbers may or may not contain alphanumeric characters. If they do, the alphanum prefix is 1-3 characters long. So, I need to parse through each bondnumber and locate the start of the numeric portion, as well as the length of the alpha portion, so I know the length and beginning position of the 0-pad. Correct?

Thanks for the response, I really appreciate your help.

Kathy

 

[MichaelRed]

Kathy,

Actually not so bad. The following will do the conversion - With a FEW caveats.

First, the Current value needs to have all of the alpha chars preceeding any of the numeric chars.

Second, there is NO provision for alternate "Pad" characters.

Basically, this just inserts the 'required' number of "0"s between the (expected) alpha and numeric parts of your input.

Public Function basPad2Ten(strIn As String) As String

    Dim AlphPart As String
    Dim NumPart As String
    Dim Idx As Integer

    If (Len(strIn) = 0) Then
        basPad2Ten = String(10, "0")
        GoTo NormExit
    End If

    For Idx = 1 To Len(strIn)
        MyChr = UCase(Mid(strIn, Idx, 1))
        If (Not (MyChr >= &quot;0&quot; And MyChr <= &quot;9&quot;)) Then
            If (MyChr >= &quot;A&quot; And MyChr <= &quot;Z&quot;) Then
                AlphPart = AlphPart & MyChr
            End If
         Else
            NumPart = NumPart & MyChr
        End If
    Next Idx

    basPad2Ten = AlphPart & String(10 - Len(strIn), &quot;0&quot;) & NumPart

NormExit:

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[kdre]

Rick,

I tried this code and it works great! Thanks again! Hopefully I'll get up to speed in VBA/Access (I'm a former PowerBuilder/Oracle developer) and soon be able to help others as you've helped me.

Kathy

 

[RickSpr]

Kathy,

Glad it works, but don't credit me--MichaelRed wrote that code for you. Rick Sprague