Prev: total repeating subreport
Next: How can I change the recordsource of a report in code?
From: helpmeplease on 29 Dec 2009 14:24
I have an employee database which tracks training. I have a table with basic
employee information and a seperate table to hold all trainings with dates
completed, hours and if it was mandatory. They are linked by an employee id.

I want to write a sum for each employee that shows the total hours earned if
the completed date falls between the user entered start and end date of the
report. Some of the trainings show regardless of the date completed if they
are identified as a mandatory training. This is my thoughts on the IIf
statement but I am missing something.

=IIf([Completed] IS BETWEEN (([Forms!frmDateSelect!Start]) AND(
[Forms!frmDateSelect!End]))),(Sum([Hours])))
From: KARL DEWEY on 29 Dec 2009 14:40
Try this --

=Sum(IIf([Completed] IS BETWEEN CVDate([Forms!frmDateSelect!Start]) AND
CVDate([Forms!frmDateSelect!End]),[Hours], 0))

--
Build a little, test a little.


"helpmeplease" wrote:

> I have an employee database which tracks training. I have a table with basic
> employee information and a seperate table to hold all trainings with dates
> completed, hours and if it was mandatory. They are linked by an employee id.
>
> I want to write a sum for each employee that shows the total hours earned if
> the completed date falls between the user entered start and end date of the
> report. Some of the trainings show regardless of the date completed if they
> are identified as a mandatory training. This is my thoughts on the IIf
> statement but I am missing something.
>
> =IIf([Completed] IS BETWEEN (([Forms!frmDateSelect!Start]) AND(
> [Forms!frmDateSelect!End]))),(Sum([Hours])))
 | 
Pages: 1
Prev: total repeating subreport
Next: How can I change the recordsource of a report in code?