If I have a report based on a simple multi column table, how can I let the readers sort by the different fields on the report by clicking in the column header (label) or clicking somewhere in the column??

As always, any guidance is greatly appreciated!

change the recordsource of the report, then requery it when they click on the label.

1. I assume you are using Access 2007 as other versions do not have clickable events on Reports.

2. A simple method would be to change the order by for the report by doing something like this in the click event of each header label:


Me.OrderBy = "[FieldNameHere], [Field2NameHere] Desc, [Field3NameHere]"
Me.OrderByOn = True

Thanks both boblarson and ajetrumpet.

I tried the code as suggested...
Me.OrderBy = "[FieldNameHere], [Field2NameHere] Desc, [Field3NameHere]"
Me.OrderByOn = True

I got this error message...
You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

I erroneously said the report was based on a table but I actually used a query within the report to sum the data. Could this be causing the error?

Thanks both boblarson and ajetrumpet.

I tried the code as suggested...
Me.OrderBy = "[FieldNameHere], [Field2NameHere] Desc, [Field3NameHere]"
Me.OrderByOn = True

I got this error message...
You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

I erroneously said the report was based on a table but I actually used a query within the report to sum the data. Could this be causing the error?

What was the ACTUAL code you used (I hope you put into place your actual field names).

Here is the code...

Private Sub UPS_Label_Click()
Me.OrderBy = "[UPS]"
Me.OrderByOn = True
End Sub

UPS is the name of the field? Perhaps you should also make sure that the CONTROL that displays UPS is not named UPS as well. Change that control name (not the label but the text box) to txtUPS.

The control for UPS is named Text96 the control source is =Sum([UPS])

The control for UPS is named Text96 the control source is =Sum([UPS])

I don't think you can set the order by in this case if the control source isn't in the query.