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?

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)

Don't bother to sort the query. The report will use its sorting and grouping options to order the recordset.

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.

This should help
Article ID: Q146310

This should help
Article ID: Q146310

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

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

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?

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

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.

If you want to change the report's sorting option, change it in the report's Open event rather than in the query. You can use global variables to store the order by clause or you can pass it to the report by using the OpenArgs argument of the OpenReport Method. Do NOT attempt to change the report from outside of the report, you would need to open it in design view and then save the design change. This will NOT work in a multi-user environment and besides, the OpenArgs requires less code.