From: Duane Hookom on 4 May 2010 00:00
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.
MS Access MVP
"Afsaneh" <Afsaneh(a)discussions.microsoft.com> wrote in message
> my xtab query is based on another select query, and the field I wanna
> is not on the xtab one, and when I try to filter the source one using a
> 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
> 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" wrote:
>> IMO, parameters should never be prompts. All user interaction should be
>> through controls on forms. Crosstab queries generally require you to set
>> parameter data types in the query.
>> Duane Hookom
>> MS Access MVP
>> "Afsaneh" <Afsaneh(a)discussions.microsoft.com> wrote in message
>> > 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" wrote:
>> >> There is a crosstab report solution at
>> >> http://www.rogersaccesslibrary.com/forum/cross-tab_topic11&SID=deee386c-4bcz-c5bdz732-37361f36-2abf9742.html.
>> >> If we knew more about your crosstab, we might be able to provide a
>> >> better
>> >> solution.
>> >> --
>> >> Duane Hookom
>> >> MS Access MVP
>> >> "Afsaneh" <Afsaneh(a)discussions.microsoft.com> wrote in message
>> >> news:C7E4D749-FD50-4867-9640-2FCE2D6231FD(a)microsoft.com...
>> >> > 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.