[AccessD] Return all values from crosstab

Hi List,

I have a batch of Access reports which are currently created and saved as
RTF files.

I now need to export the reports to Excel but have run into a major problem.

At the very top level all fields have data to return and the code I have
written outputs the whole crosstab to Excel, copies the values into another
sheet which is formatted to look like the original reports. I force null
entries in the query to return a 0 with IIf(Count([FIELD]) Is
Null,0,Count([FIELD])) so that the Excel report shows a value.

However, as the data is run against other areas, there are gaps in the data
so I cannot use the whole crosstab export approach. I will never know which
items may be present as the data changes on a weekly basis.

Example

AreaTotals	type1a data data data data
AreaTotals	type1b data data data data
AreaTotals	type2a data data data data
AreaTotals	type2b data data data data
AreaTotals	type3a data data data data
AreaTotals	type3b data data data data

Area1 type1a data data data data
Area1 type2a data data data data
Area1 type3b data data data data

Area2 type2a data data data data
Area2 type2b data data data data

Is there any way to make the crosstab output all values even if there is no
data to return ? So the third example above would look like this

Area2	type1a data data data data <<<< forced to return this line
Area2	type1b data data data data <<<< forced to return this line
Area2	type2a data data data data <<<< only data available
Area2	type2b data data data data <<<< only data available
Area2	type3a data data data data <<<< forced to return this line
Area2	type3b data data data data <<<< forced to return this line

Thanks in advance for any pointers.

Chris Swann