missing fields in reports

[rcollins]

I have a crosstab query that I am using on a report.

TRANSFORM Count(NonMedicaidQuarterReport.Access1) AS CountOfAccess1
SELECT NonMedicaidQuarterReport.[DateResolutionSent By Quarter]
FROM NonMedicaidQuarterReport
GROUP BY NonMedicaidQuarterReport.[DateResolutionSent By Quarter]
PIVOT NonMedicaidQuarterReport.Access1;

So the fields of the query whaen I run it are
DateResolutionSent By Quarter, -1 ,0. So my problem is, when I filter by quarter, some of my crosstabs no longer have the -1 collumn, and when I open the report it says it does not recognize Access1.[-1] as a field name or expression. Is there a way to supress this so I don't have the error?

[Stewart Ross]

I guess the -1 and 0 values represent True and False states, and when you filter the crosstab the True state is sometimes filtered out. This in turn leads to the report problem as the control on the report representing that field is missing a corresponding bound column in the query.

The simplest solution is to set crosstab column headings so that there will always be a true and false column present regardless of filtering. Open the query in design view of the query editor, right-click and select properties, Column Headings, and then type -1, 0 to set the property to the two values you need.

This should at least resolve the current problem for you.

Using crosstab queries in reports can be tricky, because as you have found the column headings are not always the same. Reports in general have statically-defined sets of fields, whereas crosstab headings can vary dynamically.

Setting specific crosstab headers is not always possible, as these too are set statically, limiting their use to situations that are not really typical of many real-life uses of crosstabs. It is often not possible to specify the values in advance (for example where a crosstab is used to provide totals by date period, say).

For these reasons crosstabs are not good choices as the source for reports. In your current case there is no real need to use a crosstab to feed the report - you could base a report on a non-crosstab version of the same query and I am sure you would then avoid any complications which the crosstab version has brought you. That is for another time, however.

-Stewart

[rcollins]

Thank you, works perfect.