Complex SQL sorting (1 Viewer)

[mredmond]

How complex can you get with the SQL ORDER BY clause?

In the QBE, you can only put Ascending or Descending in the Sort cell for a field. But my query is too large for QBE and I have to use SQL. It's working fine, but now I want to add an ORDER BY clause. It needs to be conditional based on a selection from my parameter form - optSortBy.

So I coded as follows:

ORDER BY switch([forms]![formname]!.optSortBy=1,[Field1],[forms]![formname]!.optSortBy=2,[Field2],[forms]![formname]!.optSortBy=3,[Field3]);

There must be something wrong, because the form won't even save the code. When I take the code out the form saves and runs fine. When the code goes in, the form will not save and even kicks me out of Access.

 

[EMP]

ORDER BY switch([forms]![formname]!optSortBy=1,[Field1], [forms]![formname]!optSortBy=2,[Field2], [forms]![formname]!optSortBy=3,[Field3]);

 

[mredmond]

Maybe I'm missing it, but I don't see a difference betwen my code and yours. Please advise.

Thanks.

 

[EMP]

Remove the dots.

 

[mredmond]

Aaahhh. Of course, you are right. But that's just my sloppy typing. I have the dots in this msg, but not in the actual code. I am afraid it's not going to be that easy.

 

[Pat Hartman]

You can use a parameter to supply a value at run time, but a parameter cannot supply a column name or other SQL component. If your query is the recordsource for a form or report, you can use the OrderBy property to specify a sort order. If you are opening the query directly (a bad idea by the way), you'll need to build the SQL in code so that you can use VBA to parse the string and replace your variable with a column name.

I suggest using a form in DataSheet view rather than giving the user a query to work with.. You'll have a lot more control.

 

[EMP]

Originally posted by mredmond:
... I have the dots in this msg, but not in the actual code. I am afraid it's not going to be that easy.

Originally posted by Pat:
... a parameter cannot supply a column name or other SQL component.

The code in the Order By clause works in the attached database.

 

[mredmond]

That's what I'm doing. A form pops up and the user checks / unchecks various radio buttons and check boxes. There are four groups of radio buttons. And to make matters worse, they kinda cascade, that is, what you choose in the first set of radio buttons causes buttons in the other groups to enable / disable. When the user finally hits SUBMIT, the underlying code is executed and that's where my code above comes in. The resulting records are fed to a report.

Everything was working fine until I put in the ORDER BY clause.

Is there a way to break it up? Could I have one query do the SELECT and then feed into another to sort and then that feed the report?

 

[Pat Hartman]

Any orderBy that you have specified in the report sorting and grouping properties will override any order by in the recordsource query.

 

[mredmond]

Just to let you know, I solved my problem.

Apparently, the ORDER BY clause had nothing to do with Access not being able to save my code. (In my gut, I didn't see how it could, but it sure appeared that way.) One of my modules got clobbered and any reference to it from some other object would could the ABEND of Access. I ended up recreating it (not so easy to remember what was in it. Learn the lesson. Back up often!!)

The Order By issue still was a problem and I solved it by removing it entirely to another Query, with the 1st Query as its source. Then I feed the sorted records to the report (which btw has no sorting or grouping to overide the query as Pat mentioned)

Thanks for all your comments. I am constantly amazed at the depth of knowledge and expertise out there. I hope I can contribute to that soon and not just ask the questions.

Salute!