Field List Values Disappear in Access report based on a Crosstab Qry

[sdelacruz8]

I have an Access 2002 report that pulls from a Crosstab query. The report works fine if I don't filter on any fields. The problem occurs when I add a filter on date (start and end date) in the Query Builder. When I add this date filter, the Field List values on the report disappear. Any idea on why this is happening?

P.S. In the Query Builder, I explicitly added the "Start" and "End" date parameters with they corresponding data type.

Thanks!

 

[dhookom]

How do you "add a filter on date"? Can you provide the SQL view of your crosstab? Do you have the Column Headings explicitly entered into your crosstab properties?

Do you realize that parameter prompts are not good user interface? faq701-6763

Duane
Hook'D on Access
MS Access MVP

 

[sdelacruz8]

I use a parameter to filter on the date field. I do have a form with a calendar, so the user can choose the date from there, but I'm just using the parameter for now.

I did explicitly enter the Column Headings into the Crosstab properties.

Here is the SQL View of the Crosstab:

TRANSFORM Sum([CallsCount Qry].CallNbr) AS SumOfCallNbr
SELECT [CallsCount Qry].plan, [CallsCount Qry].By, [CallsCount Qry].Group1, [CallsCount Qry].CallType, [CallsCount Qry].CallDte, Sum([CallsCount Qry].CallNbr) AS TotalCountofCalls
FROM [CallsCount Qry]
WHERE ((([CallsCount Qry].CallType) Is Not Null) AND (([CallsCount Qry].Type) Is Not Null))
GROUP BY [CallsCount Qry].plan, [CallsCount Qry].By, [CallsCount Qry].Group1, [CallsCount Qry].CallType, [CallsCount Qry].CallDte
PIVOT [CallsCount Qry].Type;

Thanks!

 

[dhookom]

I did explicitly enter the Column Headings into the Crosstab properties.

The Column Heading property of the SQL you provided doesn't have these entered.

I explicitly added the "Start" and "End" date parameters with they corresponding data type

I don't see these either.

Duane
Hook'D on Access
MS Access MVP