Report Runs on One Machine, but Not the Other

[croy]

A somewhat similar message was posted to
microsoft.public.access.setupconfig, but it occurrs to me
now that this might be a better place--apologies for the
duplication.

Access 2002 under WinXPPro. All SPs and security patches
applied.

On my machine, a report with a chart object that is opened
via a "starter" form, runs fine. When I copy the entire
front-end to another machine and re-link the tables, it
throws an error: "No data" (from my error-trapping). There
*is* data, however.

If I fill in the required data on the starter form, then try
to run the report from the db window, a different error
occurs: 'The Microsoft Jet database engine does not
recognize " as a valid field name or expression.'

If I delete the fields in the Detail section of the report,
it runs fine on the other machine. These fields are fed by
a crosstab query, which will run just fine by itself on the
other machine, when the starter form is filled in.

The report has two subreports. I can run all supporting
querys on either machine just fine.

The front-end uses the following modules, collected from the
websites of some of the amazing folks who contribute to
these groups:

basConcatenate
modIsLoaded
modSelectClearAll
modShowHide_dbWindow
mslVarSelected

I've stumbled onto a couple of differences in the two
machines:

VBA: Mine The "Other Machine"
6.05 6.04

Jet: 3.51.0623.0 4.00.9502.0

I don't know why these differences are there, or whether
they could be affecting this report, or how to "normalize"
them to test.

Any thoughts appreciated.

 

[Duane Hookom]

Are you setting the data types of your parameters in your crosstab queries?
Are you reporting from the exact same records?
Have you compiled the code on hte "another" machine?
Have you set the column headings property of the crosstab?

 

[croy]

Thanks for the reply, Duane!

Are you setting the data types of your parameters in your crosstab queries?

Sorry to appear daft, but I'm not sure what you mean there.
How would I go about that? Are you referring to format?

Are you reporting from the exact same records?
Yes.

Have you compiled the code on hte "another" machine?
Yes.

Have you set the column headings property of the crosstab?

I hadn't, so I just tried. When I fill in the fields on my
starter form, and then just run the crosstab, it gives me
two rows of data (good), but no column headings, whether
I've put anything in the Column Headings property, or not.
With Column Headings set, the report asks for the parameter
for each column. I'm still working on sorting that out. The
headings are times of the day, like 05:00, 06:00, etc., from
05:00 to 22:00. I probably have to wiggle the Control
Sources for the fields on the report.

Still curious why it wouldn't work on the other machine,
when I copy the entire front-end to it...

 

[Duane Hookom]

If you filter a crosstab query like Between [Enter Start Date] and [Enter End
Date], this almost always require that you set the data type of the
parameters. Select Query->Parameters from the menu.

Maybe you should share the SQL view of your crosstab so we have a better
understanding of your report and query.

 

[croy]

If you filter a crosstab query like Between [Enter Start Date] and [Enter End
Date], this almost always require that you set the data type of the
parameters. Select Query->Parameters from the menu.

Ugh! I always forget about Query->Parameters! Will check
this out.

Maybe you should share the SQL view of your crosstab so we have a better
understanding of your report and query.

You bet! But beware--I'm a novice (at best) with crosstab
querys and SQL...

qryEDMTabWkDay04 (feeds the report):

TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes =
qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT qryEDMTabWkDay02.ChartHour;

qryEDMTabWkDay03 (feeds qryEDMTabWkDay04):

SELECT qryEDMTabWkDay02.DayTypes,
Sum(qryEDMTabWkDay02.APH) AS TtlAPH
FROM qryEDMTabWkDay02
GROUP BY qryEDMTabWkDay02.DayTypes;

qryEDMTabWkDay02 (feeds qryEDMTabWkDay03):

SELECT qryEDMTabWkDay01.ChartHour,
qryEDMTabWkDay01.DayTypes,
Sum(qryEDMTabWkDay01.AnglsPerHour) AS APH
FROM qryEDMTabWkDay01
GROUP BY qryEDMTabWkDay01.ChartHour,
qryEDMTabWkDay01.DayTypes;

qryEDMTabWkDay01 (feeds qryEDMTabWkDay02):

SELECT qryEDMTabWkDayb.ChartHour,
qryEDMTabWkDayb.DayTypes,
qryEDMTabWkDayb.AnglsPerHour
FROM qryEDMTabWkDayb;

 

[Duane Hookom]

I don't see any WHERE clauses so you don't really need Query Parameters. I
expect your ChartHour values are a specific set of values that should be
included in the Column Headings property of your crosstab query.

--
Duane Hookom
Microsoft Access MVP

If you filter a crosstab query like Between [Enter Start Date] and [Enter End
Date], this almost always require that you set the data type of the
parameters. Select Query->Parameters from the menu.

Ugh! I always forget about Query->Parameters! Will check
this out.

Maybe you should share the SQL view of your crosstab so we have a better
understanding of your report and query.

You bet! But beware--I'm a novice (at best) with crosstab
querys and SQL...

qryEDMTabWkDay04 (feeds the report):

TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes =
qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT qryEDMTabWkDay02.ChartHour;

qryEDMTabWkDay03 (feeds qryEDMTabWkDay04):

SELECT qryEDMTabWkDay02.DayTypes,
Sum(qryEDMTabWkDay02.APH) AS TtlAPH
FROM qryEDMTabWkDay02
GROUP BY qryEDMTabWkDay02.DayTypes;

qryEDMTabWkDay02 (feeds qryEDMTabWkDay03):

SELECT qryEDMTabWkDay01.ChartHour,
qryEDMTabWkDay01.DayTypes,
Sum(qryEDMTabWkDay01.AnglsPerHour) AS APH
FROM qryEDMTabWkDay01
GROUP BY qryEDMTabWkDay01.ChartHour,
qryEDMTabWkDay01.DayTypes;

qryEDMTabWkDay01 (feeds qryEDMTabWkDay02):

SELECT qryEDMTabWkDayb.ChartHour,
qryEDMTabWkDayb.DayTypes,
qryEDMTabWkDayb.AnglsPerHour
FROM qryEDMTabWkDayb;

 

[croy]

I don't see any WHERE clauses so you don't really need Query Parameters. I
expect your ChartHour values are a specific set of values [...]

Correct--a table of "Survey Hours". The Hours field is a
date/time data type.

that should be included in the Column Headings property of your crosstab query.

Every time I try that, running the report causes a parameter
prompt for each of the hours.

The Column Headings property only uses text strings, right?

Could there be a data type conflict with the hour data being
date/time, and the Column Headings property being
text-strings?

 

[Duane Hookom]

I would try a crosstab of
TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes =
qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT Format(qryEDMTabWkDay02.ChartHour,"H:nn") IN
("5:00","6:00","7:00","8:00",..more here..,"21:00","22:00");
--
Duane Hookom
Microsoft Access MVP

I don't see any WHERE clauses so you don't really need Query Parameters. I
expect your ChartHour values are a specific set of values [...]

Correct--a table of "Survey Hours". The Hours field is a
date/time data type.

that should be included in the Column Headings property of your crosstab query.

Every time I try that, running the report causes a parameter
prompt for each of the hours.

The Column Headings property only uses text strings, right?

Could there be a data type conflict with the hour data being
date/time, and the Column Headings property being
text-strings?

 

[croy]

I would try a crosstab of
TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes =
qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT Format(qryEDMTabWkDay02.ChartHour,"H:nn") IN
("5:00","6:00","7:00","8:00",..more here..,"21:00","22:00");

Ok. Finally got time on the "other" machine to try this.

I put this SQL in the query (taking liberties with the line
breaks):

TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes = qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT Format(qryEDMTabWkDay02.ChartHour,"H:nn") In
("05:00","06:00","07:00","08:00","09:00","10:00","11:00",
"12:00","13:00","14:00","15:00","16:00","17:00","18:00",
"19:00","20:00","21:00","22:00");

....and it works (with one qualifier, below)! I have to
admit that I was somewhat skeptical about the prognosis.
Thank you very much for your input!

The qualifier: in the Detail section of the report, where I
show the query's output values like in a plain table, the
values for the ChartHours less than 10:00 are blank. I've
checked the Control Names, the Control Sources--all look
good, and the same convention as for the other values. I'll
keep looking at this--there's *got* to be something
different for those values... But if anyone here has ideas
on what to check, I'm all ears!

 

[croy]

Ok. Finally got time on the "other" machine to try this.

I put this SQL in the query (taking liberties with the line
breaks):

TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes = qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT Format(qryEDMTabWkDay02.ChartHour,"H:nn") In
("05:00","06:00","07:00","08:00","09:00","10:00","11:00",
"12:00","13:00","14:00","15:00","16:00","17:00","18:00",
"19:00","20:00","21:00","22:00");

...and it works (with one qualifier, below)! I have to
admit that I was somewhat skeptical about the prognosis.
Thank you very much for your input!

The qualifier: in the Detail section of the report, where I
show the query's output values like in a plain table, the
values for the ChartHours less than 10:00 are blank. I've
checked the Control Names, the Control Sources--all look
good, and the same convention as for the other values. I'll
keep looking at this--there's *got* to be something
different for those values... But if anyone here has ideas
on what to check, I'm all ears!

Update: The culprit seems to have been the leading zeros.
Once I removed those from the Column Headings property in
the query, and massaged the Control Sources in the report
accordingly, all is well.