Report based on crosstab query - problem printing

  • Thread starter Thread starter Malcolm P
  • Start date Start date
M

Malcolm P

Access 2002

Crosstab Query:
Row heading = Atty; Group by
Column heading = ChargeCode; Group by
Value = Time; sum
Row heading = Total of Time: Time; Sum
Where = TimeSheetDate

Report:
Summary of attorneys' time by charge codes.
Column headings = charge codes: PA, FAD, CLE, No Charge,
Discount, Collection, Flat Fee, Contingent Fee, Billable.
Row headings = the attorneys

If I run the report for a time period (eg: 1/1/03 to
12/31/03) in which there was time charged to all the
different charge codes, then the report prints/displays
without a problem. If I run the report for a time
period (eg: 12/1/03 to 12/31/03) in which there was no
time charged to one or more of the charge codes (eg: no
time charged to Discount cases), then I get the follow
error message, "The Microsoft Jet database does not
recognize "Discount" as a valid field name or expression.".

If I go to the Crosstab Query itself and run the query for
12/1/03 to 12/31/03, in which no time was charged to
Discount cases (for example), then the query runs without
an error and displays the query excluding a column for
Discount. The Report when run will not exclude the
Discount column, as stated above, and display the report.

How can I get the Report to print/display? Can I somehow
instruct the Report to ignore the charge code without time
charged to it and force it to display/print anyways?

Note - Report's Design:
The text boxes are in the Detail section in a row as
follows:
Atty, Total of Time, then a text box for each charge code
The control source in the charge code text boxes is =nz
([Discount],"-") This was suppose to display a - if
there was no time charged for the period.
 
Update your Column Headings property of the crosstab
Column Headings:PA, FAD, CLE, No Charge, Discount, Collection, Flat Fee,
Contingent Fee, Billable.
 
Hi Duane,

I have the same problem but I can't seem to figure out
how to make the changes to the design of the Cross Tab
query. I am in design mode for the crosstab query and the
second item in the grid is the "Column Heading" data.
Currently it says "BRegion" as the field name and in the
Tables name it says "Advisor" and in the Total row it
says "Group By" and lastly the CrossTab row says "Column
Heading". With that said I tried putting in the Field
name row "West", "East", "Central" but it would error. I
then tried Region: "West", "East", "Central" but it too
errored. Then I tried East, West, Central and that too
failed miserably. Can you help out by giving some more
detail on how to make this work. I'm probably missing
something very obvious but today it isn't so obvious to
me!

Thanks for your help!
-----Original Message-----
Update your Column Headings property of the crosstab
Column Headings:PA, FAD, CLE, No Charge, Discount, Collection, Flat Fee,
Contingent Fee, Billable.


--
Duane Hookom
MS Access MVP


Access 2002

Crosstab Query:
Row heading = Atty; Group by
Column heading = ChargeCode; Group by
Value = Time; sum
Row heading = Total of Time: Time; Sum
Where = TimeSheetDate

Report:
Summary of attorneys' time by charge codes.
Column headings = charge codes: PA, FAD, CLE, No Charge,
Discount, Collection, Flat Fee, Contingent Fee, Billable.
Row headings = the attorneys

If I run the report for a time period (eg: 1/1/03 to
12/31/03) in which there was time charged to all the
different charge codes, then the report prints/displays
without a problem. If I run the report for a time
period (eg: 12/1/03 to 12/31/03) in which there was no
time charged to one or more of the charge codes (eg: no
time charged to Discount cases), then I get the follow
error message, "The Microsoft Jet database does not
recognize "Discount" as a valid field name or expression.".

If I go to the Crosstab Query itself and run the query for
12/1/03 to 12/31/03, in which no time was charged to
Discount cases (for example), then the query runs without
an error and displays the query excluding a column for
Discount. The Report when run will not exclude the
Discount column, as stated above, and display the report.

How can I get the Report to print/display? Can I somehow
instruct the Report to ignore the charge code without time
charged to it and force it to display/print anyways?

Note - Report's Design:
The text boxes are in the Detail section in a row as
follows:
Atty, Total of Time, then a text box for each charge code
The control source in the charge code text boxes is =nz
([Discount],"-") This was suppose to display a - if
there was no time charged for the period.
Click to expand...


.
Click to expand...
 
Answered elsewhere however, make sure you have the query Properties dialog
box open to enter the column headings.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have the same problem but I can't seem to figure out
how to make the changes to the design of the Cross Tab
query. I am in design mode for the crosstab query and the
second item in the grid is the "Column Heading" data.
Currently it says "BRegion" as the field name and in the
Tables name it says "Advisor" and in the Total row it
says "Group By" and lastly the CrossTab row says "Column
Heading". With that said I tried putting in the Field
name row "West", "East", "Central" but it would error. I
then tried Region: "West", "East", "Central" but it too
errored. Then I tried East, West, Central and that too
failed miserably. Can you help out by giving some more
detail on how to make this work. I'm probably missing
something very obvious but today it isn't so obvious to
me!

Thanks for your help!
-----Original Message-----
Update your Column Headings property of the crosstab
Column Headings:PA, FAD, CLE, No Charge, Discount, Collection, Flat Fee,
Contingent Fee, Billable.


--
Duane Hookom
MS Access MVP


Access 2002

Crosstab Query:
Row heading = Atty; Group by
Column heading = ChargeCode; Group by
Value = Time; sum
Row heading = Total of Time: Time; Sum
Where = TimeSheetDate

Report:
Summary of attorneys' time by charge codes.
Column headings = charge codes: PA, FAD, CLE, No Charge,
Discount, Collection, Flat Fee, Contingent Fee, Billable.
Row headings = the attorneys

If I run the report for a time period (eg: 1/1/03 to
12/31/03) in which there was time charged to all the
different charge codes, then the report prints/displays
without a problem. If I run the report for a time
period (eg: 12/1/03 to 12/31/03) in which there was no
time charged to one or more of the charge codes (eg: no
time charged to Discount cases), then I get the follow
error message, "The Microsoft Jet database does not
recognize "Discount" as a valid field name or expression.".

If I go to the Crosstab Query itself and run the query for
12/1/03 to 12/31/03, in which no time was charged to
Discount cases (for example), then the query runs without
an error and displays the query excluding a column for
Discount. The Report when run will not exclude the
Discount column, as stated above, and display the report.

How can I get the Report to print/display? Can I somehow
instruct the Report to ignore the charge code without time
charged to it and force it to display/print anyways?

Note - Report's Design:
The text boxes are in the Detail section in a row as
follows:
Atty, Total of Time, then a text box for each charge code
The control source in the charge code text boxes is =nz
([Discount],"-") This was suppose to display a - if
there was no time charged for the period.
Click to expand...


.
Click to expand...
Click to expand...
 
Got the answer from a new post I made. Thanks again for
the quick reply!

Have a great weekend!
-----Original Message-----
Answered elsewhere however, make sure you have the query Properties dialog
box open to enter the column headings.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I have the same problem but I can't seem to figure out
how to make the changes to the design of the Cross Tab
query. I am in design mode for the crosstab query and the
second item in the grid is the "Column Heading" data.
Currently it says "BRegion" as the field name and in the
Tables name it says "Advisor" and in the Total row it
says "Group By" and lastly the CrossTab row says "Column
Heading". With that said I tried putting in the Field
name row "West", "East", "Central" but it would error. I
then tried Region: "West", "East", "Central" but it too
errored. Then I tried East, West, Central and that too
failed miserably. Can you help out by giving some more
detail on how to make this work. I'm probably missing
something very obvious but today it isn't so obvious to
me!

Thanks for your help!
-----Original Message-----
Update your Column Headings property of the crosstab
Column Headings:PA, FAD, CLE, No Charge, Discount, Collection, Flat Fee,
Contingent Fee, Billable.


--
Duane Hookom
MS Access MVP


"Malcolm P" <[email protected]>
Click to expand...
wrote
in message
Access 2002

Crosstab Query:
Row heading = Atty; Group by
Column heading = ChargeCode; Group by
Value = Time; sum
Row heading = Total of Time: Time; Sum
Where = TimeSheetDate

Report:
Summary of attorneys' time by charge codes.
Column headings = charge codes: PA, FAD, CLE, No Charge,
Discount, Collection, Flat Fee, Contingent Fee, Billable.
Row headings = the attorneys

If I run the report for a time period (eg: 1/1/03 to
12/31/03) in which there was time charged to all the
different charge codes, then the report prints/displays
without a problem. If I run the report for a time
period (eg: 12/1/03 to 12/31/03) in which there was no
time charged to one or more of the charge codes (eg: no
time charged to Discount cases), then I get the follow
error message, "The Microsoft Jet database does not
recognize "Discount" as a valid field name or expression.".

If I go to the Crosstab Query itself and run the
Click to expand...
query
for
12/1/03 to 12/31/03, in which no time was charged to
Discount cases (for example), then the query runs without
an error and displays the query excluding a column for
Discount. The Report when run will not exclude the
Discount column, as stated above, and display the report.

How can I get the Report to print/display? Can I somehow
instruct the Report to ignore the charge code
Click to expand...
without
time
charged to it and force it to display/print anyways?

Note - Report's Design:
The text boxes are in the Detail section in a row as
follows:
Atty, Total of Time, then a text box for each
Click to expand...
charge
code
The control source in the charge code text boxes is =nz
([Discount],"-") This was suppose to display a - if
there was no time charged for the period.





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top