[AccessD] Report Grouping on a derived query field

Stephen
How about trying this. Create a public function to which you pass StartDate
and which returns the correct Year. Then replace your IIF test in the query
with the function call and group on that. Might make the SQL 'simpler' so
Access can cope. Haven't tried it but may work.

--
Andy Lacey
http://www.minstersystems.co.uk



--------- Original Message --------
From: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
To: accessd at databaseadvisors.com <accessd at databaseadvisors.com>
Subject: [AccessD] Report Grouping on a derived query field
Date: 23/07/04 10:56

>
> 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
>
>
>
>
>

________________________________________________
Message sent using UebiMiau 2.7.2