CrossTab Error if data not present

[DALLASDAN]

I created a report using a crosstab query and depending on the data present,
I sometimes get an error. (The Microsoft Jet database does not recognize " as
a valid field name or expression) I have determined that its due to a "Column
Heading" not being present that I have specified on the report.

The potential columns could be "Prospect, Client, Sold, Inactive".

How can have blanks show up in columns that do not exist and not give me an
error.

Here is the code from the cross-tab query.
TRANSFORM Count([Dan Marketing Query].CustomerID) AS CountOfCustomerID
SELECT [Dan Marketing Query].[Source Description], Count([Dan Marketing
Query].CustomerID) AS [Total Of CustomerID], Sum([Dan Marketing Query].[List
Volume]) AS [SumOfList Volume], Sum([Dan Marketing Query].[List Commission])
AS [SumOfList Commission], Sum([Dan Marketing Query].[Buy Volume]) AS
[SumOfBuy Volume], Sum([Dan Marketing Query].[Buy Commission]) AS [SumOfBuy
Commission]
FROM [Dan Marketing Query]
GROUP BY [Dan Marketing Query].[Source Description]
ORDER BY [Dan Marketing Query].[Source Description]
PIVOT [Dan Marketing Query].[Lead Status];

 

[Duane Hookom]

You can fix this by adding all possible column heading values into the Column
Headings property. From your description, you might want to try something
like:

TRANSFORM Count([Dan Marketing Query].CustomerID) AS CountOfCustomerID
SELECT [Dan Marketing Query].[Source Description],
Count([Dan Marketing Query].CustomerID) AS [Total Of CustomerID],
Sum([Dan Marketing Query].[List Volume]) AS [SumOfList Volume],
Sum([Dan Marketing Query].[List Commission])
AS [SumOfList Commission],
Sum([Dan Marketing Query].[Buy Volume]) AS [SumOfBuy Volume],
Sum([Dan Marketing Query].[Buy Commission]) AS [SumOfBuy Commission]
FROM [Dan Marketing Query]
GROUP BY [Dan Marketing Query].[Source Description]
ORDER BY [Dan Marketing Query].[Source Description]
PIVOT [Dan Marketing Query].[Lead Status] IN
("Prospect","Client","Sold","Inactive");