[AccessD] Report Grouping on a derived query field

Gustav, Andy, Jim, Arthur, thanks for your input overnight, it's 8am Saturday, I'm awake, I've read your various contributions, and moved the derived field by itself to a query that is two query levels away from the report (so now it feeds the query that feeds the report).

Eureka!  Thank you one and all.  

Stephen Bond

P.S. Jim, I was reaching the stage of the temp table approach.  Not elegant is it? <g>



> -----Original Message-----
> From: Jim Dettman [mailto:jimdettman at earthlink.net]
> Sent: Friday, 23 July 2004 11:04 p.m.
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Report Grouping on a derived query field
> 
> 
> Stephen,
> 
>   Try putting the calc in a subquery.  Then build the report 
> query off that.
> I know the general rule of thumb has always been to move 
> calculations up
> rather then down, but I've run into a couple cases where it worked.
> 
>   Then there are a few cases where I actually had to build a 
> temp table in a
> temp database to feed the report<g>.
> 
> Jim.
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stephen Bond
> Sent: Friday, July 23, 2004 6:43 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Report Grouping on a derived query field
> 
> 
> Access 2000 -
> In a query which feeds a report I have a derived field.  It is derived
> directly from a date field in one of the contributing tables, thus:
> 
>   FY:Iif(Month(startDate) < 7, Year(startDate), 1+Year(startDate))
> 
> The query runs perfectly, and gives me values like 2003, 2004 
> etc in the FY
> field.
> 
> I am using this derived field, FY, as a grouping level (the 
> only grouping
> level) in the report.  When I run the report, it bombs off 
> immediately with
> the error message "The expression is typed incorrectly, or is 
> too complex to
> be evaluated  ..." and more stuff about simplifying the 
> expression. When I
> remove the Grouping Level completely the report runs.  There is no
> code-behind-forms (there will be but I have removed it to 
> attempt to solve
> this).
> 
> Any Ideas?
> 
> TIA
> Stephen Bond
> Otatara, New Zealand
> 
> 
> 
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>