problem getting last 6 months records

[balllian]

i have the following query which is looking back at the last months six records and getting a total amount for these months but it is only pulling records for the last two months ie the new year.

what is wrong with this query??

HELP much appreciated

SELECT QCount_for_chart.[Type of Breach], QCount_for_chart.[Previous No/Ref], Count(QCount_for_chart.[Previous No/Ref]) AS [CountOfPrevious No/Ref], Right([Date of discovery],2) & "/" & Mid([date of discovery],4,2) AS [Date], QCount_for_chart.[Date of Discovery]
FROM QCount_for_chart
GROUP BY QCount_for_chart.[Type of Breach], QCount_for_chart.[Previous No/Ref], Right([Date of discovery],2) & "/" & Mid([date of discovery],4,2), QCount_for_chart.[Date of Discovery]
HAVING (((QCount_for_chart.[Date of Discovery])>=DateAdd("m",-7,Date())) AND ((Mid([Date of Discovery],4,2))<Right("0" & Month(Date()),2) And (Mid([Date of Discovery],4,2))>=Right("0" & Month(Date())-6,2)))
ORDER BY Right([Date of discovery],2) & "/" & Mid([date of discovery],4,2) DESC;

 

[PHV]

Something like this ?
SELECT [Type of Breach], [Previous No/Ref], Count([Previous No/Ref]) AS [CountOfPrevious No/Ref], Format([Date of discovery],"yy/mm") AS [Date], [Date of Discovery]
FROM QCount_for_chart
WHERE Format([Date of discovery],"yyyymm") Between Format(DateAdd("m",-6,Date),"yyyymm") And Format(Date,"yyyymm")
GROUP BY [Type of Breach], [Previous No/Ref], Format([Date of discovery],"yy/mm"), [Date of Discovery]
ORDER BY Format([Date of discovery],"yy/mm") DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[balllian]

it doesnt seem to like the date field as it comes up with 'Enter parameter value' date.

 

[PHV]

Double check the spelling of the fields name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[balllian]

ive checked the spelling and everything seems to be ok.

 

[argatxa1]

Get rid of the Format in the Order By.. otherwise it considers it a string, not a date

ORDER BY [Date of discovery] DESC;