Report Based On Crosstab Query Question - A2K

Dear Access 2000 users,

I have a crosstab query that puts together certain information
perfectly. It has a criteria that is based on a form that limits how
many columns are returned based on the date selected. Here's the
problem:

The report that the crosstab query feeds was initially created to show
all the dates and thus, all 26 columns. However, since I modified the
crosstab query to only return up to what is selected on the "criteria
form", the report opens only after giving several error messages
saying can't find field. of course, this is perfectly understandable,
but is there a way around this? Either to shut the warnings off (the
ugly, incorrect way, I'm sure) on the report or, better yet, to
somehow add "dummy columns" to fill up the crosstab query so the
report won't return any errors? If there is a third option, I'm more
than willing to listen. Thanks a million in advance!

Kevin

re: Report Based On Crosstab Query Question - A2K

"No Spam" <nospam@earthlink.net> wrote in message
news:iah6i09ro1nbob0tlhpr7rkbcj1blb57gn@4ax.com...[color=blue]
> Dear Access 2000 users,
>
> I have a crosstab query that puts together certain information
> perfectly. It has a criteria that is based on a form that limits how
> many columns are returned based on the date selected. Here's the
> problem:
>
> The report that the crosstab query feeds was initially created to show
> all the dates and thus, all 26 columns. However, since I modified the
> crosstab query to only return up to what is selected on the "criteria
> form", the report opens only after giving several error messages
> saying can't find field. of course, this is perfectly understandable,
> but is there a way around this? Either to shut the warnings off (the
> ugly, incorrect way, I'm sure) on the report or, better yet, to
> somehow add "dummy columns" to fill up the crosstab query so the
> report won't return any errors? If there is a third option, I'm more
> than willing to listen. Thanks a million in advance![/color]

You can enter all possible column names in the ColumnNames property of the
appropriate column in query design view. That will force all columns to be
output even when they don't have data. It also allows you to control the
order of the columns (if that's a concern).


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Report Based On Crosstab Query Question - A2K

Rick,

Thanks a million for the quick reply - can you please elaborate on
ColumnNames? Is that when I precede the field name with a different
name and a column (NewName: [field1])? Or is there something I am
missing? And if I do this, even though some of the columns I renamed
might not be there, the query will still "fill up"? Thanks!

On Wed, 18 Aug 2004 07:39:38 -0500, "Rick Brandt"
<rickbrandt2@hotmail.com> wrote:
[color=blue]
>"No Spam" <nospam@earthlink.net> wrote in message
>news:iah6i09ro1nbob0tlhpr7rkbcj1blb57gn@4ax.com.. .[color=green]
>> Dear Access 2000 users,
>>
>> I have a crosstab query that puts together certain information
>> perfectly. It has a criteria that is based on a form that limits how
>> many columns are returned based on the date selected. Here's the
>> problem:
>>
>> The report that the crosstab query feeds was initially created to show
>> all the dates and thus, all 26 columns. However, since I modified the
>> crosstab query to only return up to what is selected on the "criteria
>> form", the report opens only after giving several error messages
>> saying can't find field. of course, this is perfectly understandable,
>> but is there a way around this? Either to shut the warnings off (the
>> ugly, incorrect way, I'm sure) on the report or, better yet, to
>> somehow add "dummy columns" to fill up the crosstab query so the
>> report won't return any errors? If there is a third option, I'm more
>> than willing to listen. Thanks a million in advance![/color]
>
>You can enter all possible column names in the ColumnNames property of the
>appropriate column in query design view. That will force all columns to be
>output even when they don't have data. It also allows you to control the
>order of the columns (if that's a concern).[/color]

re: Report Based On Crosstab Query Question - A2K

"No Spam" <nospam@earthlink.net> wrote in message
news:n9m6i01v5bb129rn0827raio5uguh12qi4@4ax.com...[color=blue]
> Rick,
>
> Thanks a million for the quick reply - can you please elaborate on
> ColumnNames? Is that when I precede the field name with a different
> name and a column (NewName: [field1])? Or is there something I am
> missing? And if I do this, even though some of the columns I renamed
> might not be there, the query will still "fill up"? Thanks![/color]

In design view of the crosstab query make sure the property sheet is being
displayed and then put focus into the design grid column that has the
Crosstab entry of "Column Heading". The property sheet should then show a
property named "Column Headings" (sorry I got it wrong in my first
response).

With your cursor in that property press <F1> to see the help topic. It's
fairly straight-forward.



--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Report Based On Crosstab Query Question - A2K

You sir, are magnificent.

Kev

On Wed, 18 Aug 2004 10:53:02 -0500, "Rick Brandt"
<rickbrandt2@hotmail.com> wrote:
[color=blue]
>"No Spam" <nospam@earthlink.net> wrote in message
>news:n9m6i01v5bb129rn0827raio5uguh12qi4@4ax.com.. .[color=green]
>> Rick,
>>
>> Thanks a million for the quick reply - can you please elaborate on
>> ColumnNames? Is that when I precede the field name with a different
>> name and a column (NewName: [field1])? Or is there something I am
>> missing? And if I do this, even though some of the columns I renamed
>> might not be there, the query will still "fill up"? Thanks![/color]
>
>In design view of the crosstab query make sure the property sheet is being
>displayed and then put focus into the design grid column that has the
>Crosstab entry of "Column Heading". The property sheet should then show a
>property named "Column Headings" (sorry I got it wrong in my first
>response).
>
>With your cursor in that property press <F1> to see the help topic. It's
>fairly straight-forward.[/color]