Crosstabs and Parameters !!??

  • Thread starter Thread starter Mark Burns
  • Start date Start date
M

Mark Burns

Ok, so you apparently can't use te automatic parameter-prompting behavior in
Access with a Crosstab query. (Damn!) <grumble> Ok.
However, that's exactly what I need for a report.
So, try to find a way to fake it.
I dynamically build a crosstab query in code, save the querydef.
That query name is used as a linked sub-query in another query with
parameter prompting.
*THUD* !!?? WHY?? The crosstab query should completely resolve itself as it
would stand-alone, so why the HECK does it blow up the entire query becaise
it is now called as a sub-query of another query that uses parameter
prompts??
$^#%$^&$!!!
 
OK, nevermind.
Reading the forum I was reminded that parameters for crosstab queries need
to be specified in the Parameters list.

However, this still doesn't answer the question as to why that is necessary
for a crosstab sub-query which itself did not reference the parameters used
elsewhere in the main query.
 
Mark-

If you create a query that uses a parameter query in the FROM clause, then
that new query inherits the parameters from the second query. So, if your
Crosstab uses as its source another query that has parameters, then you need
to supply those parameters -- and, as you have discovered, that won't work
in a Crosstab unless you explicitly declare the parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John,

I don't think you got what I was saying. I'll explain.
3 Queries:
A: a non-crosstab query with joins and parameters. - works great.
B: a crosstab query with NO parameters
C: a query combining Queries A and B.
Running C bombs out with the same error as if B were parameterized without
the parameters specified.

This does not make sense to me.
Why should C bomb out when B does not directly reference the parameters used
in A?

- Mark
 
It just does. I have found that some crosstabs that have the Column Headings
property set do not require the Query|Parameters. I haven't tested this with
all crosstabs. I don't know how crosstabs work internally. They are somewhat
unique to Access/Jet and aren't directly supported in the same way within
SQL Server (there are other options) and many other databases.

Bottom line... get used to it ;-)
 
Duane Hookom said:
It just does. I have found that some crosstabs that have the Column Headings
property set do not require the Query|Parameters. I haven't tested this with
all crosstabs. I don't know how crosstabs work internally. They are somewhat
unique to Access/Jet and aren't directly supported in the same way within
SQL Server (there are other options) and many other databases.

Bottom line... get used to it ;-)
Click to expand...

<*sigh*>
Done.
However, even when I do that, and I specify the parameters in the crosstab,
which allows the query to run, I get to something even more fun!
Please go and see:
message entitled: "Reserved error (-3007); there is no message for this
error."

in NG: microsoft.public.access.reports
 
Because when you combine Queries A and B, you end up with a crosstab query
that has parameters.

The bottom line is you should ALWAYS explicitly declare parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top