Swiftraven
Technical User
Hi,
I am writing reports based on a crosstab query with dates as column headings (ie 200011 is Nov 2000). What I need to do is be able to place a call to the query for a month/year that may not yet exist in the query. For instance, the data avalable today may only run up to 200210 and my report needs data out to 200312, and that data may be there in the future. I want to be able to just fill in with a zero value anywhere in the report where there isn't data supplied by the query yet. As it stands, I get the Microsoft Jet Engine doesnt recognize 200301 (say the first month that no data is in the query for) as a valid field name or expression.
I tried using ColumnHeadings when creating the query, but I need to be able to pick up any newer data that may come into the table for future months (ie a Future Years total) so I cant lock myself into forcing headings from 200001 out to say 200303 and then not know if data is there for 200304 and on). I also tried using IsError and IsMissing within an IIf statement on the report, but still get the same error about invalid field names.
(ie Control Source... =IIf(IsMissing(200211),0,200211)
to check to see if 200011 existed and output a 0 to the report if it didn't.
Any help is GREATLY appreciated.
Thanks
Jason
I am writing reports based on a crosstab query with dates as column headings (ie 200011 is Nov 2000). What I need to do is be able to place a call to the query for a month/year that may not yet exist in the query. For instance, the data avalable today may only run up to 200210 and my report needs data out to 200312, and that data may be there in the future. I want to be able to just fill in with a zero value anywhere in the report where there isn't data supplied by the query yet. As it stands, I get the Microsoft Jet Engine doesnt recognize 200301 (say the first month that no data is in the query for) as a valid field name or expression.
I tried using ColumnHeadings when creating the query, but I need to be able to pick up any newer data that may come into the table for future months (ie a Future Years total) so I cant lock myself into forcing headings from 200001 out to say 200303 and then not know if data is there for 200304 and on). I also tried using IsError and IsMissing within an IIf statement on the report, but still get the same error about invalid field names.
(ie Control Source... =IIf(IsMissing(200211),0,200211)
to check to see if 200011 existed and output a 0 to the report if it didn't.
Any help is GREATLY appreciated.
Thanks
Jason
