Sorting Problem in my Report

[Guest]

Hello to all,

I have a report that's bound to a query. The query uses the DatePart
function to sort on the Policy Date field. When the query runs alone the sort
works fine, but when the query is called from the report the output is
unsorted. The report gets called from my form named Control Panel. Can anyone
help me solve this?

Here is the code from the form:

Private Sub Anniversaries_AfterUpdate()
DoCmd.OpenReport "Client Anniversary", acViewPreview
End Sub

Here is the code from the query:

SELECT DISTINCTROW Clients.[First Name], Clients.[Last Name],
Clients.Address, Clients.City, Clients.State, Clients.[Zip Code],
Clients.[Policy Date]
FROM Clients
WHERE (((Month([Clients].[Policy Date]))=[Forms]![Control
Panel]![Anniversaries]))
ORDER BY DatePart("d",[Policy Date]);

 

[Graham Mandeno]

Hi Sky

For some reason, forms obey the ORDER BY clause in the RecordSource, but
reports do not. You need to go to the Sorting and Grouping window (View >
Sorting and Grouping) and define your sort criteria there.

 

[Guest]

Graham,

You were right about the Sorting and Grouping window. In my query I created
a new column called Expr1 and applied the DatePart function to sort on the
day of the week. The new column looks like this:

Expr1: DatePart("d",[Policy Date])

Then, in my report in the Sorting and Grouping window I selected the new
column from my query named Expr1 and set it to Sort Ascending. In the same
window under Group Properties I set:

Group Header = No
Group Footer = No
Group On = Each Value
Group Interval = 1
Keep Together = No

Now the report sorts chronilogically in perfect order. Thank you for
pointing me in the right direction Graham. I hope this post helps others with
similar problems.

-Sky

Hi Sky

For some reason, forms obey the ORDER BY clause in the RecordSource, but
reports do not. You need to go to the Sorting and Grouping window (View >
Sorting and Grouping) and define your sort criteria there.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello to all,

I have a report that's bound to a query. The query uses the DatePart
function to sort on the Policy Date field. When the query runs alone the
sort
works fine, but when the query is called from the report the output is
unsorted. The report gets called from my form named Control Panel. Can
anyone
help me solve this?

Here is the code from the form:

Private Sub Anniversaries_AfterUpdate()
DoCmd.OpenReport "Client Anniversary", acViewPreview
End Sub

Here is the code from the query:

SELECT DISTINCTROW Clients.[First Name], Clients.[Last Name],
Clients.Address, Clients.City, Clients.State, Clients.[Zip Code],
Clients.[Policy Date]
FROM Clients
WHERE (((Month([Clients].[Policy Date]))=[Forms]![Control
Panel]![Anniversaries]))
ORDER BY DatePart("d",[Policy Date]);