Too Complex Sum

  • Thread starter Thread starter Melissa Coffman
  • Start date Start date
M

Melissa Coffman

I have a report that shows all employees' training within a user entered start and end date and also includes all mandatory training regardless of the date. I need to show the total hours earned per employee within the date range and ignore the hours outside of that time. I built the following iif statement in the detail section which identifies the correct hours but when I try to sum it under the employee footer I get the "too complex" error message. What am I doing wrong?

IIf(Eval([Completed] Between [Forms]![frmDateSelect]![Start] And [Forms]![frmDateSelect]![End]),[hours],0)


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Multi-Lingual Language Translations
http://www.eggheadcafe.com/tutorial...d56-766a992561ef/aspnet-multilingual-lan.aspx
 
<<I need to show the total hours earned per employee within the date range
and ignore the hours outside of that time.>>

It's not clear if you want to include mandatory training (completed in the
date range) in the total! Assuming you do, you need tables that look like:
TblEmployee
EmployeeID
etc

TblTraining
TrainingID
TrainingDescription
Mandatory (Yes/No)

TblEmployeeTraining
EmployeeTrainingID
EmployeeID
TrainingID
TrainingDate

For your report you need a report and two subreports. The first subreport
shows non-mandatory training
and the second subreport shows mandatory training.

For the first subreport, create a query named QryNonMandatoryTraining based
on the three tables Set the criteria for Mandatory to NO and set the
criteria for TrainingDate to:
Between [Forms]![frmDateSelect]![Start] And [Forms]![frmDateSelect]![End]
Enter QryNonMandatoryTraining as the record source for the first subreport.
You can now easily use the running sum property on the first subform to get
the total non-mandatory training hours for each employee.

For the second subreport, create a query named QryMandatoryTraining based on
the three tables Set the criteria for Mandatory to YES and set no criteria
for TrainingDate.
Enter QryMandatoryTraining as the record source for the second subreport.
You can now easily use the running sum property on the second subform to get
the total mandatory training hours for each employee.

Steve
(e-mail address removed)
 
I'm not sure why you used Eval().
Try a control source of:
=Sum(Abs([Completed] Between [Forms]![frmDateSelect]![Start] And
[Forms]![frmDateSelect]![End]) * [hours])

If that doesn't work, try create a new column in the report's record source
RangeHrs:Abs([Completed] Between [Forms]![frmDateSelect]![Start] And
[Forms]![frmDateSelect]![End]) * [hours]

You might need to set the data type of your form/controls in the
Query->Parameters.

Also, make sure that frmDateSelect isn't closed.
 
Back
Top