Combo Sorting/MS Access Sorting

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

Are you using bound controls?

to added it i just doing

Code:

---

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

---

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

Code:

---

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.

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.

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

Code:

---

                  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 -

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