[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