SQL Group By Colum Order

[naturalsn]

Hi. I was hoping someone can assit.

I have a search form. That searches various tables and show the result in multi selelct list box. The data shown in the list box is retreived via SQL.
I am hoping to change the way the data is being displayed in the list box.

Currently the order of my colums is in no order. I have the following code to display the data.

Private Sub cmdOK_Click()
    Dim rstPersonnel As ADODB.Recordset
    Dim strSQL As String
    Dim strSkillID As String
    Dim strLocationWorked As String
    Dim strLanguages As String
    Dim varItem As Variant
    Dim l As Long
    
    On Error GoTo cmdOK_Click_Err
    
    '----- Build SQL statement -----
    strSQL = "SELECT DISTINCT PI.ID, PI.Status, PI.Surname, PI.[First Name],PI.[Status Current],PI.[Status Remark]" & _
            "FROM ((tblPersonnelInfo PI " & _
            "INNER JOIN tblPersonnelInfoSkills PIS " & _
            "ON PI.ID = PIS.PersonID) " & _
            "INNER JOIN tblPersonnelInfoWorkExp PIWE " & _
            "ON PI.ID = PIWE.ID) " & _
            "INNER JOIN tblPersonnelInfoLanguages PIL " & _
            "ON PI.ID = PIL.ID " & _
            "WHERE "


More code about searching 

 '-----  The ordering clause -----
    strSQL = strSQL & "ORDER BY PI.ID  "
    
    Set rstPersonnel = New ADODB.Recordset
    rstPersonnel.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText

    Set Me.lstPersonnel.Recordset = rstPersonnel
    
cmdOK_Click_Exit:
    DoCmd.Echo True
    Exit Sub

I currently have 6 Colums They Are

1. Id
2. Status
3. First Name
4. Surname
5. Current Status
6. Status Remark

And this is the order that i would like them to appear.
Even though i have changed where they are typed etc. They appear as

3. First Name
1. ID
2. Status
5. Current Status
6. Status Remark
4. Surname


I have added them to the order clause the way they should appear but still no order. Is there something i am possible missing.

Any advise would be appreciated.

Kind Regards
SN

 

[Remou]

How about changing this:

[tt] strSQL = "SELECT DISTINCT PI.[First Name],PI.ID, PI.Status, PI.[Status Current],PI.[Status Remark], PI.Surname" [/tt]

You will probably have to change the bound column.

 

[naturalsn]

Hi Remou

Thanks for adivse. I have changed them all around an no luck, the order still stays the same.

Bound colum change ? Currently it has a row source type Table/Query. But the listbox is unbound

 

[Remou]

That is quite strange. What happens if you just include two columns?

The bound column is relevant if you want to get a specific column returned as default.

 

[naturalsn]

Hi Remou... No luck

Created the bound for relevance as indicated. thank for that. I removed everything,
Left PI.ID, PI.Surnmae

It gave it to me in perfect order, ID then Surname

I then added First Name, it then placed
First Name first
The ID
Then Surname

So basically placing First Name before everything else.
Shuffeld around, still no change.

 

[Remou]

I suspect that the columns are being added to the listbox somewhere else. What version of Access are you using? The code does not work with Access 2000, or, apparently 2003.

 

[PHV]

Why this:
Set Me.lstPersonnel.Recordset = rstPersonnel
instead of this ?
Set Me!lstPersonnel.Rowsource = strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[naturalsn]

Hi Remou, I am using 2003
Hi PHV if i use rowsource =strSQl, I get error 424 Object Required.

i there possible a way to work around this....

 

[PHV]

Sorry for the typo:
Me!lstPersonnel.Rowsource = strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[naturalsn]

Hi

Thanks for all the help. Okay I renamed the

Set Me.lstPersonnel.Recordset = rstPersonnel

to

Me!lstPersonnel.Rowsource = strSQL

Removed

Dim rstPersonnel As ADODB.Recordset

and

Set rstPersonnel = New ADODB.Recordset
    rstPersonnel.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText

my code looks like this and upon selecting Command OK, nothing appear in my list box..

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub cmdOK_Click()

    Dim strSQL As String
    Dim strSkillID As String
    Dim strLocationWorked As String
    Dim strLanguages As String
    Dim varItem As Variant
    Dim l As Long
    
    On Error GoTo cmdOK_Click_Err
    
    '----- Build SQL statement -----
    strSQL = "SELECT DISTINCT PI.ID, PI.Surname, PI.[First Name] " & _
            "FROM ((tblPersonnelInfo PI " & _
            "INNER JOIN tblPersonnelInfoSkills PIS " & _
            "ON PI.ID = PIS.PersonID) " & _
            "INNER JOIN tblPersonnelInfoWorkExp PIWE " & _
            "ON PI.ID = PIWE.ID) " & _
            "INNER JOIN tblPersonnelInfoLanguages PIL " & _
            "ON PI.ID = PIL.ID " & _
            "WHERE "

    '----- Add the Skills -----
    For Each varItem In Me.lstskill.ItemsSelected
        strSkillID = strSkillID & ",'" & Me.lstskill.ItemData(varItem) & "'"
    Next varItem
    
    If Len(strSkillID) = 0 Then
        strSkillID = "LIKE '%'"
    Else
        strSkillID = Right(strSkillID, Len(strSkillID) - 1)
        strSkillID = "IN (SELECT ID FROM lktblSkills WHERE SkillID IN (" & strSkillID & "))"
    End If
    
    strSQL = strSQL & "PIS.SkillID " & strSkillID & " "

    '----- Add the Locations Worked -----
    For Each varItem In Me.lstLocWorked.ItemsSelected
        strLocationWorked = strLocationWorked & ",'" & Me.lstLocWorked.ItemData(varItem) & "'"
    Next varItem

    If Len(strLocationWorked) = 0 Then
        strLocationWorked = "LIKE '%'"
    Else
        strLocationWorked = Right(strLocationWorked, Len(strLocationWorked) - 1)
        strLocationWorked = "IN (" & strLocationWorked & ")"
    End If

    'Add the And/Or
    strSQL = strSQL & UCase(Me.cboLocations.Value) & " "
    
    strSQL = strSQL & "PIWE.LocationWorked " & strLocationWorked & " "
    
    '----- Add the Languages -----
    For Each varItem In Me.lstLanguage.ItemsSelected
        strLanguages = strLanguages & ",'" & Me.lstLanguage.ItemData(varItem) & "'"
    Next varItem
    If Len(strLanguages) = 0 Then
        strLanguages = "LIKE '%'"
    Else
        strLanguages = Right(strLanguages, Len(strLanguages) - 1)
        strLanguages = "IN (" & strLanguages & ")"
    End If

    'Add the And/Or
    strSQL = strSQL & UCase(Me.cboLanguages.Value) & " "
    
    strSQL = strSQL & "(PIL.[Language] " & strLanguages & ") "
    
    '----- Add the ordering clause -----
    strSQL = strSQL & "ORDER BY PI.Surname,PI.[First Name]"
    
Me!lstPersonnel.RowSource = strSQL
    
cmdOK_Click_Exit:
    DoCmd.Echo True
    Exit Sub
cmdOK_Click_Err:
    MsgBox "An unexpected error has occurred." _
        & vbCrLf & "Procedure: cmdOK_Click" _
        & vbCrLf & "Error Number: " & Err.Number _
        & vbCrLf & "Error Description:" & Err.Description _
        , vbCritical, "Error"
    Resume cmdOK_Click_Exit
End Sub

 

[PHV]

Replace this:
strLanguages = "LIKE '%'"
with this:
strLanguages = "LIKE '*'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sm0res]

The DISTINCT operator doesn't preserve ordering... I am surprised the code even works. If you remove the DISTINCT operator, you would see everything ordered as indicated by the ORDER BY statement.