More Record Numbering

[Quicky]

I have a table and a report where one of the fields is a numbering system of the following form: 1.1, 1.2, 1.3, etc. The field is a text field so that I can have 1.1.3, 1.1.4 etc. However, when I reach 1.10, (meaning one point ten), the record is placed just after 1.1, instead of after 1.9 which is where I would like it to be. How can I get around this sorting problem?

Thanks,
Quicky

 

[MichaelRed]

There are several options here. If there are not very many records, you can do an easy soloution. Set up an input format to match what you really want in the field. This will FORCE the entry of the correct number of characters in each sub-field. Look at the social security and phone number examples for the input format. They show this type of input formatting quite well. Of course, you still need to 'correct' the existing entries. This requires either:

A.[tab]Manual review and adjustment of all current records

or

B.[tab]generation of a simple parseing routine to update existing records to the new format.

Assuming that all sub-fields shaould be two (2) characters:

Public Function basTwoChr(strIn As String) As String

    'Sample Usage
    '? basTwoChr("1.1.3")
    '01.01.03


    MyVar = Split(strIn, ".")

    For Idx = 0 To UBound(MyVar)
        MyVar(Idx) = right("00" & MyVar(Idx), 2)
    Next Idx

    For Idx = 0 To UBound(MyVar) - 1
        strOut = Join(MyVar, ".")
    Next Idx
    basTwoChr = strOut
    

End Function

will construct a two character subfield for the input.

You cna use the function to update the existing field or to create a calculated field used for the actual sorting - while keeping the current 'free-form' field for user input (although I would not recommend the latter).

MichaelRed
mred@att.net

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

 

[Quicky]

Brilliant. Thanks for the help.