Filter not working in cross tab query?

[vengsiva]

I have a table like this
Cultureworkload
Reasonfortesting IRLNumber Diagnostic sputum specimens processed Follow up specimens processed Reception
Follow up 7/2013 0 1 02-Jan-13
Diagnosis 8/2013 1 0 02-Jan-13
Follow up 9/2013 1 0 02-Jan-13
Diagnosis 10/2013 1 0 02-feb-13
Follow up 11/2013 1 0 03-feb-13
Diagnosis 14/2013 1 0 03-Mar-13
Diagnosis 15/2013 1 0 03-Apr-13

I built a query with the wizard to group by Reason for testing
The SQl was
TRANSFORM Count(Cultureworkload.IRLNumber) AS CountOfIRLNumber
SELECT Cultureworkload.Reasonfortesting, Count(Cultureworkload.IRLNumber) AS [Total Of IRLNumber]
FROM Cultureworkload
GROUP BY Cultureworkload.Reasonfortesting
PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Altough I filtered reception field by month to 1st quarter it shows empty months like Apr,May Jun etc.Please help
vengsiva

 

[CazB]

I'm no expert on crosstabs but I think it could be because of

PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

When you put IN like that, you are forcing it to include all those months as headings... regardless of whether there is any data to display for those months

 

[vengsiva]

Thanks It works if I rewrite PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar") for quarter 1 similarly for other quarters.I am now trying to work out way to generate a query with VBA which will work when user inputs a quarter for which he wants the cross tab by clicking on a listbox
vengsiva

 

[JHB]

The format function can also return quarter, (1, 2, 3 and 4) using "q" instead of "mmm".

Format([Reception],"q")