Printing crosstab query

[Afsaneh]

Dear All,
I would like to print a cross tab query directly.I need to change the date
and title of it.but don't know How.since my cross tab query is not monthly
and my columns are changing,I can't make a report out of it, that's why I
wanna print it directly. tnx for your advice in advance.

 

[Duane Hookom]

There is a crosstab report solution at
http://www.rogersaccesslibrary.com/...deee386c-4bcz-c5bdz732-37361f36-2abf9742.html.
If we knew more about your crosstab, we might be able to provide a better
solution.

 

[Afsaneh]

Dear duane,
I need customer names as columns and statistics of products as rows.I
managed to customize Your instance and use it. I really enjoyed undrestanding
and customizung your report especially your LEVEL trick. It wasn't confusing
at all.TNX a million.
I just have the problem of filtering my xtab query. first, when using
parameters access warns that it doesnt recognize my parameters as valid field
names or expressions. and on a parametered report from a crosstab query
,access asks for each parameter twice . again tnx for your guidance.

 

[Duane Hookom]

IMO, parameters should never be prompts. All user interaction should be
through controls on forms. Crosstab queries generally require you to set the
parameter data types in the query.

 

[Afsaneh]

my xtab query is based on another select query, and the field I wanna filter
is not on the xtab one, and when I try to filter the source one using a form
to get the parameters ,I get the message from access "the microsoft jet
database engine does not recognize '[Forms]![StaRep]![txtSal]' as a valid
field name or exp"!
here are my sql :
"SELECT QfullStationary.*, Left$(QfullStationary!EnterDate,4) AS sal,
Mid$(QfullStationary!EnterDate,5,2) AS mah, QfullStationary.OmorAlias,
QfullStationary.LEVEL, Forms!StaRep!txtSal AS Expr1
FROM QfullStationary
WHERE (((Left$([QfullStationary]![EnterDate],4))=[Forms]![StaRep]![txtSal]));"

and

TRANSFORM Sum(QStaMahSal.Qtty) AS SumOfQtty
SELECT QStaMahSal.QfullStationary.LEVEL, QStaMahSal.NameGroup,
QStaMahSal.NameKala, QStaMahSal.Moavenat, QStaMahSal.MoavenatId,
Sum(QStaMahSal.Qtty) AS [جمع تعداد]
FROM QStaMahSal
GROUP BY QStaMahSal.QfullStationary.LEVEL, QStaMahSal.NameGroup,
QStaMahSal.NameKala, QStaMahSal.Moavenat, QStaMahSal.MoavenatId
PIVOT QStaMahSal.QfullStationary.OmorAlias;

I just open my report from the form.tnx

 

[Duane Hookom]

As I noted before "Crosstab queries generally require you to set the
parameter data types in the query". This is even true if the crosstab is
based on a query with a parameter/criteria.

--
Duane Hookom
MS Access MVP

my xtab query is based on another select query, and the field I wanna
filter
is not on the xtab one, and when I try to filter the source one using a
form
to get the parameters ,I get the message from access "the microsoft jet
database engine does not recognize '[Forms]![StaRep]![txtSal]' as a valid
field name or exp"!
here are my sql :
"SELECT QfullStationary.*, Left$(QfullStationary!EnterDate,4) AS sal,
Mid$(QfullStationary!EnterDate,5,2) AS mah, QfullStationary.OmorAlias,
QfullStationary.LEVEL, Forms!StaRep!txtSal AS Expr1
FROM QfullStationary
WHERE
(((Left$([QfullStationary]![EnterDate],4))=[Forms]![StaRep]![txtSal]));"

and

TRANSFORM Sum(QStaMahSal.Qtty) AS SumOfQtty
SELECT QStaMahSal.QfullStationary.LEVEL, QStaMahSal.NameGroup,
QStaMahSal.NameKala, QStaMahSal.Moavenat, QStaMahSal.MoavenatId,
Sum(QStaMahSal.Qtty) AS [جمع تعداد]
FROM QStaMahSal
GROUP BY QStaMahSal.QfullStationary.LEVEL, QStaMahSal.NameGroup,
QStaMahSal.NameKala, QStaMahSal.Moavenat, QStaMahSal.MoavenatId
PIVOT QStaMahSal.QfullStationary.OmorAlias;

I just open my report from the form.tnx

IMO, parameters should never be prompts. All user interaction should be
through controls on forms. Crosstab queries generally require you to set
the
parameter data types in the query.