Resultset sorting problem

[Guest]

Here's the SQL:

SELECT tblIDTComment.idFeedbackNumber, tblIDTComment.DateofComment, ' -- ' &
[tblTeamMembers.TeamMemberFirstName] & ': ' & [IDTComment] AS NameAndComment
FROM tblIDTComment INNER JOIN tblTeamMembers ON tblIDTComment.IDTeamMember =
tblTeamMembers.IDTeam
ORDER BY tblIDTComment.idFeedbackNumber, tblIDTComment.DateofComment;


This is producing a result set ordered first, in an ascending order, on
idFeedbackNumber. A secondary sort, on DateofComment, is ordered in a
descending order. I would like to change this so that the DateofComment
field is sorted in an ascending order also.

On a lark, I've tried the following but it doesn't work (result set is
identical):

SELECT tblIDTComment.idFeedbackNumber, tblIDTComment.DateofComment, ' -- ' &
[tblTeamMembers.TeamMemberFirstName] & ': ' & [IDTComment] AS NameAndComment
FROM tblIDTComment INNER JOIN tblTeamMembers ON tblIDTComment.IDTeamMember =
tblTeamMembers.IDTeam
ORDER BY tblIDTComment.idFeedbackNumber, tblIDTComment.DateofComment DESC;

(Note that the DateofComment is a General Date format item but oddly enough
the time doesn't show in the resultset so I can't check that.)

 

[John Spencer (MVP)]

Your query should be sorting correctly.

Are you by any chance using this query as the source of a report? If so, then
you need to know that a report IGNORES any sort order imposed by the query. In
the report, you must use the Sorting and Grouping dialog to set up the sort order.

If you aren't using this in a report, then I am stumped by the results you are getting.

 

[Guest]

Nope; the query serves one purpose: to allow the result set to appear in a
textbox.

Your query should be sorting correctly.

Are you by any chance using this query as the source of a report? If so, then
you need to know that a report IGNORES any sort order imposed by the query. In
the report, you must use the Sorting and Grouping dialog to set up the sort order.

If you aren't using this in a report, then I am stumped by the results you are getting.

Here's the SQL:

SELECT tblIDTComment.idFeedbackNumber, tblIDTComment.DateofComment, ' -- ' &
[tblTeamMembers.TeamMemberFirstName] & ': ' & [IDTComment] AS NameAndComment
FROM tblIDTComment INNER JOIN tblTeamMembers ON tblIDTComment.IDTeamMember =
tblTeamMembers.IDTeam
ORDER BY tblIDTComment.idFeedbackNumber, tblIDTComment.DateofComment;

This is producing a result set ordered first, in an ascending order, on
idFeedbackNumber. A secondary sort, on DateofComment, is ordered in a
descending order. I would like to change this so that the DateofComment
field is sorted in an ascending order also.

On a lark, I've tried the following but it doesn't work (result set is
identical):

SELECT tblIDTComment.idFeedbackNumber, tblIDTComment.DateofComment, ' -- ' &
[tblTeamMembers.TeamMemberFirstName] & ': ' & [IDTComment] AS NameAndComment
FROM tblIDTComment INNER JOIN tblTeamMembers ON tblIDTComment.IDTeamMember =
tblTeamMembers.IDTeam
ORDER BY tblIDTComment.idFeedbackNumber, tblIDTComment.DateofComment DESC;

(Note that the DateofComment is a General Date format item but oddly enough
the time doesn't show in the resultset so I can't check that.)