parameter query

[Wim]

Hi all,

I have an examination result report based on a crosstab
query (E2), which itself is based upon a parameter select
query (E1). I want to be able to limit the results to
e.g. the spring classes in 2003.

So I have in the criterion row in E1 : Like [give the
course code e.g. 04*]. In the query parameters window I
ask in the parameter column : [give the course code e.g.
04*] and in the data type column : text.

When I type 03* I get the results of all the exams for
2003; when I type 03S* I get all the results for he spring
classes; when I type 03F* I get all the fall results, so
this works fine. When I run E2 : this works fine as well.

But when I want to print the report based upon E2 !

If I give for the parameter 03* : I get the results of
all the exams for 2003. If, however, I type 03S* I get an
error message : The Microsoft Jet-databse-engine can't
recognize as a valid fieldname or expression. The Help
button refers to error 3070. In the query parameter
window in E2 I have the same parameter + data type as in
E1.

The coursecode field is a row heading in my query. So I
can't describe columheading properties ??

But what I don't understad is that 03* works but 03s* not !

Thanks for your answer,

Wim

 

[John Spencer (MVP)]

Your problem is probably in the REPORT layout. Reports based on crosstab queries
often fail since the crosstab will not reliably return the same columns when the
parameter value changes. If you have a control in the report bound to a column
"CHEM101" which is only taught in the fall then when the report attempts to run
the spring results it cannot find a column "CHEM101" and therefore the report
will fail even though the query successfully ran.

There are methods to solve this problem. Try using google and searching for
responses by Duane Hookom in the reports newsgroup containing the words Crosstab
reports. You should get a ton of hits and a possible solution.

If I have mis-diagnosed your problems, post back here.