Combo Sorting/MS Access Sorting

[Jmay12]

Hey guys i have a program here that is a invoice program now my combo box is laid out as
year then number
ex. 12-32
but now if i goto
12-100
its laid out as
12-1
12-100
12-89
12-99

insted of
12-99
12-100
12-101

and the database does the same
now how can i make it sort i dont mind making a function to sort but dont know where to start i have looked around but havent been able to find somthing that I understand

[d.paulson]

Are you using bound controls?

[Jmay12]

to added it i just doing

strSql = "SELECT * FROM [Invoice]"
        Set rst = OpenSQL(strSql)
        
            With rst
                    
                Do Until .EOF
                    frmInvoice.comInvoice.AddItem !InvoiceNum
                    .MoveNext
                Loop
                .Close
            End With

[DataMiser]

The issue is that it is doing an alpha sort 100 comes before 2 since the first character is a 1 compared to a 2

To get an alpha sort to show in numeric order you must pad leading 0s

002 comes before 100 2 comes after 100

[d.paulson]

strSql = "SELECT * FROM [Invoice] ORDER BY len(cstr(!InvoiceNum)), !InvoiceNum"

If InvoiceNum is a numeric. If InvoiceNum is a string than the cstr is not needed. Make sure the combobox sorted property is set to False otherwise it'll resort back to alphanumeric.

[DataMiser]

The only thing is that sorting by length may not do the job since 12-89 and 12-99 are the same length you may or may not get the order you want.

Typically invoice numbers increment in sequence so maybe the solution is not to use a sort at all.

[DataMiser]

You could change the code that adds to the combo to something like

                   strTemp = Mid$(!InvoiceNum, 1, 3) & Right$("000" & Mid$(!InvoiceNum, 4), 3)
                    frmInvoice.comInvoice.AddItem strTemp

Should sort the way you want then though would need to be adjusted if there could be more than 3 digits to the right of the -

[Jmay12]

Hey guys thanks for the replys i used the 002
works perfect now thanks guys