Allow users to select sort order of a report from a form

[neeko1226]

I have a report that is distributed all over the place. It was in excel which allowed everyone to sort the data how they wanted to see it. Now it's in MS Access 97, so the report is built into the database and accessed by a command button on a form.

I would like to allow them to sort it how they need it by selecting up to 5 sorting options (combo boxes) from the form before clicking the command button. If they select all five, the report sorts in that order, if only 2 are selected, then the report only sorts by the 2 selected...and so on.

Anyone have any thoughts on the best way to do this? I would really like to avoid creating an individual report for every possible sort order, as there are over 50.

Thanks in advance,

Neeko

 

[RonRepp]

I'm no Access expert (far from it), but if you use ADO (it might be DAO in Access), you simply concatenate a SQL string to make your report and the final line would be the sort order. Something like this:

SQL="SELECT * FROM tblCustomers " 'The space at the end is important

if combo5.text <> "" AND combo4.text <> "" AND combo3.text <> ""  AND combo2.text <> "" AND combo1.text <> ""  then
  SQL=SQL & "ORDER BY " & combo1.text & ", " & combo2.text & ", " & combo3.Text & ", " & combo2.text & ", " & 
combo1.text & ";"

elseif combo4.text <> "" AND combo3.text <> ""  AND combo4.text <> "" AND combo5.text <> ""  then

SQL=SQL & "ORDER BY " & combo1.text & ", " & combo2.text & ", " & combo3.Text & ", " & combo4.text & ";"

etc...

End If

Using an IF statement may not be the easiest way, but you'll have to figure out certain things, like what if a user only populates combo5 and not combo1, etc.

I hope this helps.




Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[neeko1226]

I was going to try something similar by changing the query definition, but I'm not sure that will change the sort order of the report.

 

[VicRauch]

The report is in charge of the sort order of the report. Changing the query does not help at all, sort of.

What I have done is created a work file for the report, only because the queries get too complicated for Access to run. Not always, but enough times, I just do the work file from the beggining.

OK, here is the procedure: (based on your 5 fields)
Put whatever fields you need into the workfile to feed the report, then add 5 fields (Sort1, Sort2, Sort3, Sort4, and Sort5) Based on the request by the user, put the value from their requested first sort field into Sort1, 2nd requested sort field value into Sort2, etc. These fields are in addition to ALL the fields your report needs. The only thing the report will do with these 5 Sort fields is sort the report.
Then, in the report, using the Sorting And Grouping box, put Sort1, Sort2, etc. as the 5 fields that control the sorting of the report.

 

[VicRauch]

I forgot to mention, if you have any numeric fields that will be part of the sort order, you will need to make sure that each numeric value translates to be the same length so that after being put into a text field (sort1-5) the numberic value will still sort correctly.

 

[neeko1226]

Not exactly sure what you mean by "workfile"?

 

[VicRauch]

Sorry, it would be a table that you would fill the records for your report that would normally come from a query. For example, if you only have one query that needs to be run before your report, change it to an Append query and write all these records into this "workfile". This new table would only be a work-table, which I sometimes call a "workfile" which only shows how old I am. I started back when a table was a piece of furniture, and a file was something that held records.

HTH,
Vic

 

[neeko1226]

Ok, I see what your saying. One last question. How do you get the value of the chosen sort field populated for each record in Sort1, Sort2, ....etc

For example, one of the fields they can choose to sort by is Banker Name. If they select Banker Name as Sort1, how would I get the Banker Name for each record populated into the Sort1 field without hard-coding Sort1 to always look for the value in the Banker Name field?

Sorry to be such a pest.

 

[VicRauch]

The way I have done it is to build the SQL in VBA code.

I would give the users 5 combo boxes where they could choose which field that want to use in which sort position.

Then the code would work something like this for the Update query.

SQL = "UPDATE Table1 SET Sort1=[" & Me!cboSort1 & "], " & _
SET Sort2=[" & Me!cboSort2 & "], " & _
SET Sort3=[" & Me!cboSort3 & "], " & _
etc.
DoCmd.RunQuery SQL

Good Luck!
Vic

 

[neeko1226]

That will only populate the Sort1 with the name of the field the user wants to sort by.

In the example I gave above, each record would have a string value of "Banker Name" in Sort1. If I then sorted the report based on Sort1, it would see all the records as equal and would not sort them.

What I don't understand how to do is populate the actual Banker's name from the "Banker Name" field into Sort1, for example:

Sort1

rec1: Alf
rec2: Baby Einstine
rec3: Papa Smurf
rec4: Tupac Shakur
rec5: Zelda

Sorry if I wasn't clear about that. When I first tried to create what you described, I was able to get Sort1 populated with the string "Bankers Name" for all records, but I can't get the above to work without hard-coding Sort1 to always display the value in the Bankers Name field.

 

[VicRauch]

Actually, the SQL
[blue]SQL = "UPDATE Table1 SET Sort1=[" & Me!cboSort1 & "], " & _
SET Sort2=[" & Me!cboSort2 & "], " & _
SET Sort3=[" & Me!cboSort3 & "], " & _
etc.[/blue]
comes out looking like this:
SQL = "UPDATE Table1 SET Sort1=[BankCityName], " & _
SET Sort2=[BankerName];"
Note that the "[]" (brackets) around the field name tells the query processor to use the value from the field (BankCityName and BankerName) to populate the Sort1 and Sort2 fields.

The thing I left out is how to handle the blanks in cboSort1 - 5. So, here that is:
SQL = "UPDATE Table1 SET "
If Me!cboSort1 <> "" Then
SQL = SQL & Sort1 = [" & Me!cboSort1 & "], "
End
If If Me!cboSort2 <> "" Then
SQL = SQL & Sort2 = [" & Me!cboSort2 & "], "
End If

At the end of all 5 If statements, you will need to remove the last two characters from the SQL statement. That would be the comma and the blank space, and then insert a semi-colon ( and the SQL statement is finished and ready to run.

Any more question, please ask.
Vic

 

[neeko1226]

Ahhhhhhhh.....

I have seen the light! Thanks so much. I really appreciate your time.