[AccessD] report sorting problem

Susan, 

Ditch the report's grouping properties. Use something like this in the
query: 

  Year([tblTopics].[DateDue]) * 100 +
DatePart("ww",[tblTopics].[DateDue]) AS GroupSort 

You can then group by GroupSort, and still order your records by
DateDue. I lost my patience with Access' report grouping properties a
long time ago. 

-Ken

-----Original Message-----
From: Susan Harkins [mailto:harkinsss at bellsouth.net] 
Sent: Sunday, March 19, 2006 11:20 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] report sorting problem

I have a grouped report based on the following query:
 
SELECT tblTopics.Title, tblTopics.DateDue,
tblPublishersContacts.Contact, tblPartners.LastName,
tblTopics.EstimatedFee,
=IIf(IsNull([LastName]),[EstimatedFee],[EstimatedFee]/2) As SplitFee
FROM tblPartners INNER JOIN (tblPublishersContacts INNER JOIN tblTopics
ON tblPublishersContacts.PublisherContactID =
tblTopics.PublisherContactID) ON tblPartners.PartnerID =
tblTopics.PartnerID WHERE (((tblTopics.DateDue) Is Not Null) AND
((tblTopics.DateSubmitted) Is
Null))
ORDER BY tblTopics.DateDue
UNION SELECT Title, DateDue, Contact, LastName, EstimatedFee, 0 FROM
tblScheduledItems ORDER BY tblTopics.DateDue;
 
The report is grouped on the DateDue field and I use the report's
grouping properties to group by the week. 
 
When I run the query, it sorts by the DateDue values, but the grouped
report loses that sort. The records group properly, I just can't get
them to sort properly within the group. 
 
Any help?
Susan H.