Suming a Calulated field

E

eanyea

I have two tables
1. With details of people
2. With events for these people
the events are two feilds Start date and end date. These tables are related
one to many (i.e: one person can have lots of start and end dates).
With help I have managed to calulate the dates between the start and end
dates using a query with the formula
Interval: Nz([End Date],Date())-[Start Date]
This works fine.
Now my problem:
I want to sum the start and end dates (e.g: if a person had start and end
dates of 2 days and 7 days I want to sum these two to make 9)
I have used a form for the person details and in it a sub form for the start
and end dates with the interval formula showing each sum for each start and
end date.
I have tried putting in the footer
=Sum([Interval])
but that does not work. I am not fussed if the sum is in a query or a form.
I will eventually have to write reports if that helps
Please help
 
M

Marshall Barton

eanyea said:
I have two tables
1. With details of people
2. With events for these people
the events are two feilds Start date and end date. These tables are related
one to many (i.e: one person can have lots of start and end dates).
With help I have managed to calulate the dates between the start and end
dates using a query with the formula
Interval: Nz([End Date],Date())-[Start Date]
This works fine.
Now my problem:
I want to sum the start and end dates (e.g: if a person had start and end
dates of 2 days and 7 days I want to sum these two to make 9)
I have used a form for the person details and in it a sub form for the start
and end dates with the interval formula showing each sum for each start and
end date.
I have tried putting in the footer
=Sum([Interval])
but that does not work. I am not fussed if the sum is in a query or a form.
I will eventually have to write reports if that helps
Click to expand...


You can not Sum a control on a form/report.

You can, however, Sum the expression you used to calculate
the form's control. E.g.

=Sum(Nz([End Date],Date())-[Start Date])
 
Top