[AccessD] Transform query question

Took a look at the MSKB and found the following article:

ACC2000: Error When Running Crosstab Query with a Parameter
http://support.microsoft.com/default.aspx?scid=kb;en-us;209778&Product=acc20
00

Jet is confusing your form control parameters with column names.  In the
Query designer, select Query > Parameters, and enter the name of the
controls (i.e. [Forms]![Report Form]![Start_Date]) as parameters with the
appropriate data type, and all will be well.

So there.  We both learned something today...


-----Original Message-----
From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
Sent: Monday, February 23, 2004 9:48 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Transform query question


I get an error --
Microsoft Jet Database Engine does not recognise '[Forms]![Report
Form]![Start_Date]' as a valid field name of expression
It works fine when the lest line is present :--\


> Oleg,
> This is the default functionality of cross-tab queries (i.e. supress
> blank columns).  It is overridden by specifying an IN clause in the
> PIVOT statement, to force the specfied columns to show regardless of
> whether or not they contain data.
>
> Omit the In ("Jan","Feb"... from the query and it should return what you
> want, e.g.:
>
> TRANSFORM Count([Ron Data enf].ID) AS CountOfID
> SELECT [Ron Data enf].REFERDEPTorSRO AS Origin, Count([Ron Data enf].ID)
> AS Total
> FROM [Ron Data enf]
> WHERE (((IIf([Ron Data enf]![Date Case Ref Received]>=[Forms]![Report
> Form]![Start_Date] And [Ron Data enf]![Date Case Ref


> Received]<=[Forms]![Report Form]![End_Date],"Cases Closed")) Is Not
> Null)) GROUP BY [Ron Data enf].REFERDEPTorSRO
> PIVOT Format([AMTS Date Opened],"mmm");
>
> -----Original Message-----
> From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
> Sent: Monday, February 23, 2004 9:18 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Transform query question
>
>
> Good Morning Group
>
> I have a transform query, and  I would like for it to show only the
> columns that actually contain data, so if there is no data for September
> and October, just show August column  followed by November.  Is it
> possible ?
>
> Thank yo
> Oleg
>
> TRANSFORM Count([Ron Data enf].ID) AS CountOfID
> SELECT [Ron Data enf].REFERDEPTorSRO AS Origin, Count([Ron Data enf].ID)
> AS Total
> FROM [Ron Data enf]
> WHERE (((IIf([Ron Data enf]![Date Case Ref Received]>=[Forms]![Report
> Form]![Start_Date] And [Ron Data enf]![Date Case Ref
> Received]<=[Forms]![Report Form]![End_Date],"Cases Closed")) Is Not
> Null)) GROUP BY [Ron Data enf].REFERDEPTorSRO
> PIVOT Format([AMTS Date Opened],"mmm") In
> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
>
>
>
> -----------------------------------------
> Get Breaking News from CNN, ABC, NBC, CBS Now.
> http://www.xuppa.com/news/?link=webmail
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
----------------------------------------------------------------------------
----------------------------------------
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom
> they are addressed.
> If you have received this email in error please notify the
> originator of the message. This footer also confirms that this
> email message has been scanned for the presence of computer viruses.
>
> Any views expressed in this message are those of the individual
> sender, except where the sender specifies and with authority,
> states them to be the views of Tappe Construction Co.
>
> Scanning of this message and addition of this footer is performed
> by SurfControl E-mail Filter software in conjunction with
> virus detection software.
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-----------------------------------------
Get Breaking News from CNN, ABC, NBC, CBS Now.
http://www.xuppa.com/news/?link=webmail


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--------------------------------------------------------------------------------------------------------------------
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this email in error please notify the
originator of the message. This footer also confirms that this
email message has been scanned for the presence of computer viruses.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

Scanning of this message and addition of this footer is performed
by SurfControl E-mail Filter software in conjunction with 
virus detection software.