ORDER BY question

[tmar89]

I am writing an SQL statement to generate a query and then open a report:

' Run the query and report
MyDB.QueryDefs.Delete "qryCustom"
Set qdf = MyDB.CreateQueryDef("qryCustom", strSQL)
DoCmd.OpenReport "rptCustom", acPreview

my SQL statement ends with an ORDER BY statement and the fieldnames can change based on the form decision

strSQL = ... ORDER BY getFieldName(Me.sort0) ASC, getFieldName(Me.sort1) ASC

However when I run the code and the report opens the data isn't sorted. Why doesn't this work?

 

[Fizzio]

strSQL = ... ORDER BY getFieldName(Me.sort0) ASC, getFieldName(Me.sort1) ASC?

The Quotes will be important in this. The correct quotation method should be

strSQL = "... ORDER BY "& getFieldName(Me.sort0) &" ASC, " & getFieldName(Me.sort1) & " ASC"

But also what is passed from getFieldName(Me.sort0) will have to be a valid feild name (and table qualifier if necessary)

 

[tmar89]

In my form, I generate a query that is dynamic. I can select any of the fields I want to sort by. So for instance I could have a query that has:

strSQL= "... ORDER BY LastName ASC, FirstName ASC;"

The query generated is sorted however when the report open it up, its not sorted. I don't understand why. Do I have to set some properties in the report? I don't just want one option to sort and group with the report.

 

[Rich]

This should help
Article ID: Q146310

 

[tmar89]

This should help
Article ID: Q146310

How do I search for a particular article on this forum?

 

[Rich]

That article is in the Access help file, or should be

 

[tmar89]

Got the article, thanks.. but here's what I notice. The query generated is sorted exactly how I want it and I just want the report to do no such sorting or grouping. I want it to just open the query as the query is. How come when I open a report linked to a query, it reorders the data around even though I set up no sorting?

 

[Rich]

You have to use the Sort/Grouping options of the report

 

[tmar89]

Well, I think there's something buggy with Access then. I can't explain why this is happening here. If I create a report based on my query and do absolutely no sorting or grouping with the report, it opens up my query as is. If I change the sorting in my query by the ORDER BY statement and then reopen my report it changes the order like I want. However after a while the report starts opening up the data in an unsorted way. It's weird.