Listbox order

[Wayne]

Hi

I have a listbox that lists all of the job numbers, eg:
LM2-004
LM2-002
LM2-001
LM1-054
3-009
3-008

I am currently using the following code on the On Open
event of the form to provide the source data for the listbox:
Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
strSQL = "SELECT ProjectID, ProjectName " & _
"FROM Projects "
If Me.HideCompleted = True Then
strSQL = strSQL & " WHERE StatusID <>5 "
End If
Me.JobNumber.RowSource = strSQL & _
" ORDER BY ProjectID DESC"
End Sub

This works fine and lists all of the job numbers in
descending order. What I want to do is have the Job Numbers
that start with a number appear first in the list but still
have them all in descending order. Eg:
3-009
3-008
LM2-004
LM2-002
LM2-001
LM1-054
Is there any way I can do this?

 

[Michel Walsh]

Hi,


.... ORDER BY 0<>val(ProjectID ), ProjectID DESC



will order the projects starting with a sequence of number first. The sort
is still lexicographical, not numerical, 10-xxx is still between 1-xxxx
and 2-xxxx. Look at val( ), in the help file, to get more idea, and remember
the ORDER BY can order on expression, and on more than one "column".


Hoping it may help,
Vanderghast, Access MVP