I am trying to create a query that will get me the recordset of a date range for the quarterly chart.
I created quarterly query(qryRGAqtr) based on the monthly table(tblRGAmo), the quarterly query converted the date(mm/dd/yyyy) to quarter (q yyyy)format and sorted by year and quarter order.

The qryRGAqtr produced the right results
4Q 1998
1Q 1999
2Q 1999
3Q 1999
3Q 2002

but when I added the date range criteria to the same query it pulls out the wrong data and on top of it, it sorted by the quarter then the year.
This is my query:

SELECT Left(Format([tblRGAmo].[Date],"q yyyy"),1) & "Q " & Right(Format([tblRGAmo].[Date],"q yyyy"),4) AS [Date], Sum([tblRGAmo].[Apple]) AS Apple, Sum([tblRGAmo].[Other]) AS Other, Sum([tblRGAmo].[# Shipments]) AS [# Shipments], Format(Sum([tblRGAmo.Apple])/Sum([tblRGAmo]![# Shipments]),"Percent") AS [Apple %], Format(Sum([tblRGAmo]![Other])/Sum([tblRGAmo]![# Shipments]),"Percent") AS [Other %]
GROUP BY Format([tblRGAmo].[Date],"q yyyy"), (DatePart("yyyy",[tblRGAmo.Date])), (DatePart("q",[tblRGAmo.Date]))
HAVING Left(Format([tblRGAmo].[Date],"q yyyy"),1) & "Q" & Right(Format([tblRGAmo].[Date],"q yyyy"),4) Between forms!frmQuarterDateRange1!txtStartingQtr And forms!frmQuarterDateRange1!txtEndingQtr
ORDER BY (DatePart("yyyy",[tblRGAmo.Date])), (DatePart("q",[tblRGAmo.Date]));

If forms!frmQuarterDateRange1!txtStartingQtr is 4Q 1999
And forms!frmQuarterDateRange1!txtEndingQtr is 2Q 2000

The query produced:
2Q 1999
3Q 1999
2Q 2000
3Q 2000
2Q 2001
3Q 2001
2Q 2002
3Q 2002

The correct results should be:
4Q 1999
1Q 2000
2Q 2000

Somehow I think the "Date" field in the table became a text type after converted the normal date to quarter format in the query. But shouldn"t still be able to find the range eventhough it's a text field since the txt boxes are also in text?
What puzzles me is that the query produced the results in the right order before. Why did it change results order?
I tested the statements over and over again and still couldn't get right results.
Does anyone has any ideas?
I would appreciated any help!