Crosstab problems with Nulls and column headings

[Homer J]

I have a problem with a Crosstab query in Access97.

I'm trying to get a crosstab query to have set column headings (Inception Date,Q,C) as this query then feeds another query and its expecting these column headings.

The status field in my table will be filled with either Q or C. It all works fine but when there are no Q's or C's in the table (ie, no records in the table)I lose one of my column headings. In this situation I want to keep the column headings & have it show zeros instead. This is what I've got so far

TRANSFORM IIf(Count([ref_no]) Is Null,0,Count([ref_no])) AS Expr1
SELECT [TBL InceptionDate].Inception_date
FROM [TBL InceptionDate] LEFT JOIN [TBL DR VA] ON [TBL InceptionDate].Inception_date = [TBL DR VA].inception_date
GROUP BY [TBL InceptionDate].Inception_date
ORDER BY IIf([TBL DR VA]![status] Like 'Q*','Q','C') DESC
PIVOT IIf([TBL DR VA]![status] Like 'Q*','Q','C');

 

[namliam]

Use the "IN" operator...

TRANSFORM IIf(Count([ref_no]) Is Null,0,Count([ref_no])) AS Expr1
SELECT [TBL InceptionDate].Inception_date
FROM [TBL InceptionDate] LEFT JOIN [TBL DR VA] ON [TBL InceptionDate].Inception_date = [TBL DR VA].inception_date
GROUP BY [TBL InceptionDate].Inception_date
ORDER BY IIf([TBL DR VA]![status] Like 'Q*','Q','C') DESC
PIVOT IIf([TBL DR VA]![status] Like 'Q*','Q','C') in ('Q','C');

Regardz