Puzzle - query gives different results ??

Y

ym

year month month long month count
2004 1 January 1
2004 2 February 1
2004 3 March 1
2004 5 May 1
2004 6 June 1
2004 7 July 1
2004 9 September 2

this is my results if I were to use the SQL query from strReportSQL in the
query window and this is good.

strReportSQL = "SELECT TEMP.Year, " & _
" TEMP.Month, " & _
" tbl_Month.[English Description] as [Month
Long], " & _
" Count(*) as [Month Count] " & _
"FROM ( SELECT DatePart('yyyy',[Date Received]) AS
[Year], " & _
" Datepart('m',[Date Received]) AS
[Month], " & _
" [Date Received], " & _
" [Tracking Number] " & _
" FROM SalesReports " & _
" WHERE [Date Received] >= #1/1/2000# " & _
" AND [Date Received] <= #2004-10-10# " & _
" ) AS TEMP " & _
"INNER JOIN [tbl_Month] " & _
"ON [TEMP].[Month] = [tbl_Month].[Month
Number] " & _
"GROUP BY TEMP.year, TEMP.month,
tbl_Month.[English Description] "


However in my report, it shows me this result set and I have the
Report.RecordSource = strReportSQL

Year Month Count
===============
2004 May 1
June 1
January 1
September 1
February 1
October 1
July 1
August 1
April 1
March 1

The problems have been bugging me since Friday and I can't figure where it
went wrong :
1) the month is not ordered ascending
2) the count is wrong

Can some one please help ?

ym
 
Top