"If" statement based on a checkbox

[Ben Feingold]

I have two databases Attendance and Class List. There is a relationship connecting them based on [color:"blue"] Student Name . A drop down menu in the Attendance database defines if the student is "tardy" and a check box defines if the tardy was "excused" (0=not excused, 1=excused).

The Class List database uses the following "If" statement to count the number of tardys and subtract 1 if the tardy is excused:

If(Attendance::Excused=1, Sum(Attendance::#Tardy1)-1,Sum(Attendance::#Tardy1))

This formula works for the first occurance of a student but if there is a second tardy it doesn't subtract. When I check off the box, the sum stays at 1...

Thoughts?

-Ben

[RalphL]

Why wouldn't you just use Sum(Attendance::#Tardy1)-Sum(Attendance::Excused)? No IF statement.

[Ben Feingold]

Origionally I had done that but it wasn't counting all the occurances over multiple records. I now have a field that counts the tardys and another that determines if it is excused. Then it finds the difference. My brain is working in Excel and trying to translate it to FM...sigh...

My new problem is as such:

Let's I have 200 records in the behavior database. I only want my report to return results for the last 14 days. How do I limit the report to this range?

- Ben