Report Calculations Error

[J. Trucking]

Hello,

What I thought was a problem with the way I entered dates in has
turned out to be a problem with a report calculation. I am creating a
form to which a user can input various search criteria for a machine
that crushes rock (dates of crushing, customer, location, etc.). When
the search button is hit, it launches a query. It references records
from a table with have all of the information on them that the user
inputs as well as the following:

Engine Hours at the Start of the Day
Engine Hours at the End of the Day
Tonnes Crushed at the Start of the Day (A scale on the machine keeps
track of the total tonnes crushed)
Tonnes Crushed at the End of the Day

What I did in the query is set up three additional expressions (Expr1,
Expr2, and Expr3) with the following formulas:

Expr1: IIf(IsError(([TonneEnd]-[TonneStart])/([EngineEnd]-
[EngineStart])),0,([TonneEnd]-[TonneStart])/([EngineEnd]-
[EngineStart]))

Expr2: [TonneEnd]-[TonneStart]

Expr3: [EngineEnd]-[EngineStart]

What I am attempting to do is display (in the report) the hours used
during the day (Expr3), the tonnes crushed in the day (Expr2), and the
production in tonnes/hr for that day. By using text boxes in the
report, I am able to achieve this. I have also added on the report,
text boxes at the end to calculate the total hours used for all of the
records displayed, as well as the toal tonnes, and average
production. I did this by the following:

Total Tonnes: =Sum([Expr2])
Total Hours: =Sum([Expr3])
Average Production: =Sum([Expr1])
(I only put everything past the equal sign in the text box in the
report footer)

This method has no problems generating the report when the criteria
are narrowed down to the point where only one entry exists. However,
when I broaden the range to the point to where I know more than one
entry exists, I get the error: "The expression is typed incorreclty or
is too complex to be evaluated...". Any thoughts to why this is
occuring?

Thanks for any help,

John

 

[Guest]

Post the SQL of your query the report uses.

 

[J. Trucking]

PARAMETERS [Forms]![frmCrusherSearch]![StartDate] DateTime, [Forms]!
[frmCrusherSearch]![EndDate] DateTime;
SELECT tblCrusherReport.Date, tblCrusherReport.Customer,
tblCrusherReport.Material, tblCrusherReport.Bar,
tblCrusherReport.Location, tblCrusherReport.EngineStart,
tblCrusherReport.BarStart, tblCrusherReport.TonneStart,
tblCrusherReport.EngineEnd, tblCrusherReport.BarEnd,
tblCrusherReport.TonneEnd, tblCrusherReport.Notes,
IIf(IsError(([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart])),0,
([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart])) AS Expr1,
[TonneEnd]-[TonneStart] AS Expr2, [EngineEnd]-[EngineStart] AS Expr3
FROM tblCrusherReport
WHERE (((tblCrusherReport.Date) Between [Forms]![frmCrusherSearch]!
[StartDate] And [Forms]![frmCrusherSearch]![EndDate]) AND
((tblCrusherReport.Customer)=[Forms]![frmCrusherSearch]![Customer])
AND ((tblCrusherReport.Material)=[Forms]![frmCrusherSearch]!
[Material]) AND ((tblCrusherReport.Bar)=[Forms]![frmCrusherSearch]!
[Bar]) AND ((tblCrusherReport.Location)=[Forms]![frmCrusherSearch]!
[Location]));