How to sort (ORDER BY) a query (1 Viewer)

[anb001]

I have a query, which look like this:

SELECT tblSJAHeader.Department, tblSJAHeader.SJANumber, tblSJAHeader.SJATitle FROM tblSJAHeader
ORDER BY tblSJAHeader.xxxxxxxxxxx;

The ORDER BY part should be according to a selection in a combo box, meaning if "Department" is selected, then query should be "ORDER BY tblSJAHeader.Department", if "SJANumber" is selected, then "ORDER BY tblSJAHeader.SJANumber" etc.

Is it possible to change the query to reflect above?

Thanks.

 

[jdraw]

Yes, but you need some vba.
An option would be to have 3 queries - one for each possible sort order.

Then have a Combo with value list
Department, SJANumber, SJATitle

Depending on which selection is made, use a Select Case statement to execute the appropriate query.

see http://www.techonthenet.com/access/functions/advanced/case.php

 

[projecttoday]

Forms have an order by property. If you're opening a form, you could set that instead.

 

[anb001]

It is actually a report, by I assume it should still work with the ORDER BY property.

This is what I have created, but there is no sorting done.

Private Sub cmdPrintList_Click()
Dim strListOrder As Integer

strListOrder = Me.cboListOrder.Value

Select Case strListOrder
            
    Case 1
       DoCmd.OpenReport "rptListSJA", acPreview
        Reports!rptListSJA.OrderBy = "SJATitle"
    Case 2
        DoCmd.OpenReport "rptListSJA", acPreview
        Reports!rptListSJA.OrderBy = "SJANumber"
    Case 3
        DoCmd.OpenReport "rptListSJA", acPreview
        Reports!rptListSJA.OrderBy = "Department"
End Select
    
End Sub

If I leave out the second line in each case statement, and write direvtly in the Order By property for the report before I run it, then it is sorting, so I assume it is the second line which is wrong somehow.

 

[jdraw]

Your original question was for a query, your question now is about a Report.

see http://msdn.microsoft.com/en-us/library/aa220304(v=office.11).aspx
for some Report Open parameters.

 

[anb001]

Ok. Will 'close' this post, and continue on report forum.

 

[Pat Hartman]

FYI - Access is smarter than us and so completely rewrites the query you use as the RecordSource for a report and ignores any column not bound to a control on the report and also ignores any order by clause since that is also defined by the report.