[AccessD] Transform query question

Oleg_123 at xuppa.com Oleg_123 at xuppa.com
Mon Feb 23 09:47:36 CST 2004


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





More information about the AccessD mailing list