Sorting report Generated from SQL String

[trickg]

I have a dynamic report that is generated from a search form. It gives the user the ability to set their criteria on many fields and sort the report according to the field they wish to see it sorted on. At the moment, it populates the grid on the form just fine in the correct sorting order. The form generates an SQL string based on the what the user has selected. When the report is previewed/printed, the report's RecordSource property is set to the SQL string generated by the form, the problem is, even though there is sorting in the SQL string, the records are dropped into the report randomly.

The report will inherit attributes from a table or form, but I can't seem to get it to inherit the sorting from the SQL string.

Any ideas?

 

[Pat Hartman]

Reports control their own sorting since any grouping you specify will affect the sequence required, so sorting in the recordsource query will not accomplish anything except to waste time since the recordset will be sorted twice. You'll need to set the report's sort property with VBA in the Open event.

 

[trickg]

Well, thanks. I'd gotten that far. What I don't know is how to code it and I was hoping that someone knew.

I've tried using the OrderBy property but I can't seem to get that to work. I've tried to hard code it to one of the field names, but I keep getting errors and I'm unable to find a source for the correct syntax.

Again, any suggestions would be helpful.

 

[LQ]

At the risk of sounding simplistic, can't you use the sorting and grouping tool? I also have a report that is generated by an SQL statement, and I put an ascending sort on one of the fields using that dialog box and it seemed to work.

 

[trickg]

Well, it's a good question but the reason that I can't use it is because the field that is sorted on is decided upon by the user. They have a combo box with the various fields to sort on (default is Item_ID) and they also get to choose from another list, which direction they want the sort to go. It does me no good to use the sorting and grouping tool because then it is no longer flexible.

Your method would work if the user always wanted it sorted by the same field, but the reason that I created the search/custom report form was to give the user to grab almost any data by any criteria they wanted to set.

[This message has been edited by trickg (edited 02-07-2002).]

 

[Happy YN]

Can't you duplicate the repoert for each of the fields possible to sort by and change the sorting and grouping in each one accordingly then write code so depending on which field the user has chosen in the combobox it will open the correct report

 

[trickg]

I suppose that I could do that, but there are 12 different sortable fields, both ascending and descending and the client wants to add a second sort. If, by duplicating and creating an instance for every combination, I'd end up with a lot of reports. I may not add the second sort to the form, it isn't crucial, but even with the one sort, I'm looking at 24 different reports. It's doable, but I would rather make it dynamic in the code.

If I can figure out the proper syntax for it, I can add the second sort feature, work out the variables in the VB, and only have to use one report.

 

[trickg]

Ok, I've figured out my own problem so I'm going to post my findings now that it is done. The issue that I was having wasn't so much knowing what to do, but knowing the proper syntax to get it to fly.

In the report's Open event, you just set the OrderBy property to the SQL "Order By" but the syntax must be as follows.

Private Sub Report_Open(Cancel As Integer)
Dim sortSQL As String

'The brackets "[" and "]" are important
'I couldn't get it to work without them.
'This will later be dynamically generated
'in the form's code and passed to the report.
sortSQL = "[LastName] ASC"

'"g_sSQL" is the global query string
'dynamically generated that produces the
'report.
Me.RecordSource = g_sSQL

'Sorts the Report on the correct field.
Me.OrderBy = sortSQL
End Sub


That's it in a nutshell. This will save some of you some work later on because it will give you some flexibility to ad an ad hoc capability for your users to the application. I would guess that you could even do grouping and other things of that sort dynamically although that isn't required for this project so I won't know until later.

One last thing. The Field name that you put in the brackets is the name of the field on the report, not necessarily the name of field in the table supplying the data, which could be named differently.


[This message has been edited by trickg (edited 02-08-2002).]

 

[Happy YN]

sounds very useful! am looking forward to using it its certainly better than 24 reports! well done

 

[Happy YN]

just a minute! so how does the user decide the order is there an if statement that assigns a value to sortSQL depending on a combobox selection?