Problem with crosstab report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to run a 'Total Sales by Day' report from a crosstab query. The
crosstab query is based on an underlying query which normally works fine, so
long as data exists for each day of the week. However, problems occur when,
for example, I adjust the underlying query to only report figures for the
last few days. Access then displays the following error message:

"The Microsoft Jet database engine does not recognize 'Sunday' (or whichever
day cannot be found) as a valid field name or expression".

The 'detail' section of the report contains 7 fields, with the control
sources set to refer to each day of the week in the crosstab query, eg.
"Sunday".

The group footers each contain another 7 fields with Sum formulae entered
for each day, for example:

=Sum([Sunday]), =Sum([Monday]), and so on...

Is there any way I can make this report more flexible without having to
adjust it each time and remove the offending fields?

Regards,

Jedster
 
Open the crosstab query in design view.
Open the Properties box (View menu).
Beside the Column Headings property, list the names of the column headings,
i.e.:
Sunday, Monday, Tuesday, ...

This forces the query to create the columns, even when they have no data.
Because the column name is present, the report does not barf.
 
Thanks Allen, that works great.

Regards,

Jedster


Allen Browne said:
Open the crosstab query in design view.
Open the Properties box (View menu).
Beside the Column Headings property, list the names of the column headings,
i.e.:
Sunday, Monday, Tuesday, ...

This forces the query to create the columns, even when they have no data.
Because the column name is present, the report does not barf.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jedster said:
I am trying to run a 'Total Sales by Day' report from a crosstab query.
The
crosstab query is based on an underlying query which normally works fine,
so
long as data exists for each day of the week. However, problems occur
when,
for example, I adjust the underlying query to only report figures for the
last few days. Access then displays the following error message:

"The Microsoft Jet database engine does not recognize 'Sunday' (or
whichever
day cannot be found) as a valid field name or expression".

The 'detail' section of the report contains 7 fields, with the control
sources set to refer to each day of the week in the crosstab query, eg.
"Sunday".

The group footers each contain another 7 fields with Sum formulae entered
for each day, for example:

=Sum([Sunday]), =Sum([Monday]), and so on...

Is there any way I can make this report more flexible without having to
adjust it each time and remove the offending fields?

Regards,

Jedster
Click to expand...
Click to expand...
 
Allen,
I am having a similar problem. My report, which is based on a cross tab
query gave me the error

microsoft jet engine does not recognize "

I tried your suggestion to Jebster, adding each field name in the column
headings property box. This worked, I no longer got the jet engine error.
However, I don't want my report to show every column. My column headings are
employee names, the rows are dates with the calculated field being time for
that employee that date. I don't want an employee to show up on the report
when they do not have time. Can I fix th is report, make the jet engine
error not happen, but not list every employee in my report?

Here is my query SQL:

PARAMETERS [Start Date? MM/DD/YY] DateTime, [End Date? MM/DD/YY] DateTime;
TRANSFORM Sum(TblTime.Hours) AS SumOfHours
SELECT TblTime.Date, Sum(TblTime.Hours) AS [Total Of Hours]
FROM TblTime
WHERE (((TblTime.Date) Between [Start Date? MM/DD/YY] And [End Date?
MM/DD/YY]))
GROUP BY TblTime.Date
PIVOT TblTime.Employee In ("AMS","DEA","LB","LVA","MLM","WFC");

Any help would be greatly appreciated!



Allen Browne said:
Open the crosstab query in design view.
Open the Properties box (View menu).
Beside the Column Headings property, list the names of the column headings,
i.e.:
Sunday, Monday, Tuesday, ...

This forces the query to create the columns, even when they have no data.
Because the column name is present, the report does not barf.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jedster said:
I am trying to run a 'Total Sales by Day' report from a crosstab query.
The
crosstab query is based on an underlying query which normally works fine,
so
long as data exists for each day of the week. However, problems occur
when,
for example, I adjust the underlying query to only report figures for the
last few days. Access then displays the following error message:

"The Microsoft Jet database engine does not recognize 'Sunday' (or
whichever
day cannot be found) as a valid field name or expression".

The 'detail' section of the report contains 7 fields, with the control
sources set to refer to each day of the week in the crosstab query, eg.
"Sunday".

The group footers each contain another 7 fields with Sum formulae entered
for each day, for example:

=Sum([Sunday]), =Sum([Monday]), and so on...

Is there any way I can make this report more flexible without having to
adjust it each time and remove the offending fields?

Regards,

Jedster
Click to expand...
Click to expand...
 
You might want to review the crosstab report samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
MS Access MVP


Amanda said:
Allen,
I am having a similar problem. My report, which is based on a cross tab
query gave me the error

microsoft jet engine does not recognize "

I tried your suggestion to Jebster, adding each field name in the column
headings property box. This worked, I no longer got the jet engine error.
However, I don't want my report to show every column. My column headings
are
employee names, the rows are dates with the calculated field being time
for
that employee that date. I don't want an employee to show up on the
report
when they do not have time. Can I fix th is report, make the jet engine
error not happen, but not list every employee in my report?

Here is my query SQL:

PARAMETERS [Start Date? MM/DD/YY] DateTime, [End Date? MM/DD/YY]
DateTime;
TRANSFORM Sum(TblTime.Hours) AS SumOfHours
SELECT TblTime.Date, Sum(TblTime.Hours) AS [Total Of Hours]
FROM TblTime
WHERE (((TblTime.Date) Between [Start Date? MM/DD/YY] And [End Date?
MM/DD/YY]))
GROUP BY TblTime.Date
PIVOT TblTime.Employee In ("AMS","DEA","LB","LVA","MLM","WFC");

Any help would be greatly appreciated!



Allen Browne said:
Open the crosstab query in design view.
Open the Properties box (View menu).
Beside the Column Headings property, list the names of the column
headings,
i.e.:
Sunday, Monday, Tuesday, ...

This forces the query to create the columns, even when they have no data.
Because the column name is present, the report does not barf.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jedster said:
I am trying to run a 'Total Sales by Day' report from a crosstab query.
The
crosstab query is based on an underlying query which normally works
fine,
so
long as data exists for each day of the week. However, problems occur
when,
for example, I adjust the underlying query to only report figures for
the
last few days. Access then displays the following error message:

"The Microsoft Jet database engine does not recognize 'Sunday' (or
whichever
day cannot be found) as a valid field name or expression".

The 'detail' section of the report contains 7 fields, with the control
sources set to refer to each day of the week in the crosstab query, eg.
"Sunday".

The group footers each contain another 7 fields with Sum formulae
entered
for each day, for example:

=Sum([Sunday]), =Sum([Monday]), and so on...

Is there any way I can make this report more flexible without having to
adjust it each time and remove the offending fields?

Regards,

Jedster
Click to expand...
Click to expand...
Click to expand...
 
Back
Top