-
June 6th, 2012, 12:42 PM
#1
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
-
June 6th, 2012, 01:20 PM
#2
Re: Combo Sorting/MS Access Sorting
Are you using bound controls?
-
June 6th, 2012, 01:34 PM
#3
Re: Combo Sorting/MS Access Sorting
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
-
June 6th, 2012, 05:25 PM
#4
Re: Combo Sorting/MS Access Sorting
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
Always use [code][/code] tags when posting code.
-
June 6th, 2012, 06:19 PM
#5
Re: Combo Sorting/MS Access Sorting
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.
-
June 6th, 2012, 07:36 PM
#6
Re: Combo Sorting/MS Access Sorting
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.
Always use [code][/code] tags when posting code.
-
June 6th, 2012, 07:43 PM
#7
Re: Combo Sorting/MS Access Sorting
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 -
Always use [code][/code] tags when posting code.
-
June 9th, 2012, 08:29 PM
#8
Re: Combo Sorting/MS Access Sorting
Hey guys thanks for the replys i used the 002
works perfect now thanks guys
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|