Hi all.
I have 2 reports that both feed off the same query. They are called from a very simple menu where a start date and end date are captured. Then click one of three buttons as shown here
If I run the query it works fine and returns all rows as expected. If I run either of the reports I get the following error:
Run-Time error 3071
This expression is typed incorrectly, or it is too complicated to be evaluated. For example a numeric expression may contain too many complicated elements. Try simplifying the expression y assigning parts of the expression to variables.
Only problem is that it's not giving me any clues as to which expression.
Here is the query which runs with no problem in its own right:
PARAMETERS [Forms]![frm Run Reports and Queries]![txtStartDate] DateTime, [Forms]![frm Run Reports and Queries]![txtEndDate] DateTime;
SELECT Updates.FrameWork_ID, [Service User].SU_Name, Updates.Update_Date, Updates.End_Date, Updates.Update_Reason, Updates.One_Off_Cost, Updates.Change_AT_Costs, Updates.Change_Costs_Saved, Updates.Change_Costs_Avoided, GetDaysBetween(Forms![frm Run Reports and Queries]!txtStartDate,Forms![frm Run Reports and Queries]!txtEndDate,[Update_Date],Nz([End_Date],Date())) AS DaysActive, [Service User].SU_Name, FormatCurrency(((Updates.Change_AT_Costs / 7) * DaysActive)) AS Total_AT_Costs, FormatCurrency((Updates.Change_Costs_Saved/7 * DaysActive)) AS Total_Costs_Saved, FormatCurrency((Updates.Change_Costs_Avoided/7 * DaysActive)) AS Total_Costs_Avoided
FROM [Service User] INNER JOIN Updates ON [Service User].FrameWorkID = Updates.FrameWork_ID
WHERE (((GetDaysBetween(Forms![frm Run Reports and Queries]!txtStartDate,Forms![frm Run Reports and Queries]!txtEndDate,[Update_Date],Nz([End_Date],Date())))>0))
ORDER BY Updates.FrameWork_ID;
And in the summary report I just have 6 fields that get evaluated and the expressions behind each one are:
=FormatCurrency(Sum([Total_AT_Costs]))
=FormatCurrency(Sum([Total_Costs_Saved]))
=FormatCurrency(Sum([Total_Costs_Avoided]))
=FormatCurrency(Sum([One_Off_Cost]))
=(SELECT Count(*) FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates]))
=Count([End_Date]>0)
So if the query runs fine and as you can see there is nothing particularly complex about the expressions in the calculated fields on the report, can anyone suggest where to look? And not forgetting that both the reports were running fine a couple of weeks ago and haven't been changed. All I have done is seperate the front end and back end databases to make it more network share friendly.
Many thanks
David