User sort in a report

[Helen]

Is there a way to allow the user to sort a column in a report? I have 3
numerical columns in a report, need to sort the report by any one of those
columns. Right clicking on the field gives filter options but they don't
include smallest to largest sorting. Is there any way to set it up so the
user can sort whichever column they need?

 

[Allen Browne]

You can use code to set the ControlSource of the Grouplevel.

Details in:
Sorting report records at runtime
at:
http://allenbrowne.com/ser-33.html

 

[Helen]

I was afraid you were going to say that, I don't have any experience with
coding but I'll give it a try. Thank you Allen.

 

[KARL DEWEY]

Here is another way --
Use a separate query for each sort type and add two fields to the queries.
One field named Sort uses the input from form as criteria. My example below
prompts for 1, 2, or 3. The other is labeled SortBy with the sorted field
for source. Put the queries in a union all query and ORDER BY the SortBy
field.

SELECT abcus.ID, abcus.region, abcus.[region-start-date], abcus.xx, 1 AS
Sort, abcus.ID AS SortBY
FROM abcus
WHERE (((1)=[Enter Sort]))
ORDER BY abcus.ID;

SELECT abcus.ID, abcus.region, abcus.[region-start-date], abcus.xx, 2 AS
Sort, abcus.region AS SortBY
FROM abcus
WHERE (((2)=[Enter Sort]))
ORDER BY abcus.region;

SELECT abcus.ID, abcus.region, abcus.[region-start-date], abcus.xx, 3 AS
Sort, abcus.[region-start-date] AS SortBy
FROM abcus
WHERE (((3)=[Enter Sort]))
ORDER BY abcus.[region-start-date];

SELECT ID, region, [region-start-date], xx, SortBy
FROM Sort_1
UNION ALL SELECT ID, region, [region-start-date], xx, SortBy
FROM Sort_2
UNION ALL SELECT ID, region, [region-start-date], xx, SortBy
FROM Sort_3
ORDER BY SortBy;

 

[Klatuu]

I don't think so, Karl.

Reports do not honor the sort order of their record source queries. It has
to be done using Sorting and Grouping in the report.

Here is another way --
Use a separate query for each sort type and add two fields to the queries.
One field named Sort uses the input from form as criteria. My example
below
prompts for 1, 2, or 3. The other is labeled SortBy with the sorted field
for source. Put the queries in a union all query and ORDER BY the SortBy
field.

SELECT abcus.ID, abcus.region, abcus.[region-start-date], abcus.xx, 1 AS
Sort, abcus.ID AS SortBY
FROM abcus
WHERE (((1)=[Enter Sort]))
ORDER BY abcus.ID;

SELECT abcus.ID, abcus.region, abcus.[region-start-date], abcus.xx, 2 AS
Sort, abcus.region AS SortBY
FROM abcus
WHERE (((2)=[Enter Sort]))
ORDER BY abcus.region;

SELECT abcus.ID, abcus.region, abcus.[region-start-date], abcus.xx, 3 AS
Sort, abcus.[region-start-date] AS SortBy
FROM abcus
WHERE (((3)=[Enter Sort]))
ORDER BY abcus.[region-start-date];

SELECT ID, region, [region-start-date], xx, SortBy
FROM Sort_1
UNION ALL SELECT ID, region, [region-start-date], xx, SortBy
FROM Sort_2
UNION ALL SELECT ID, region, [region-start-date], xx, SortBy
FROM Sort_3
ORDER BY SortBy;

--
KARL DEWEY
Build a little - Test a little

I was afraid you were going to say that, I don't have any experience with
coding but I'll give it a try. Thank you Allen.