calculating the amounts between dates

[Moosh]

I have a database with two tables
1. table containing details of people
2. table containing two fields of which – Start Date and end date

These tables are joined one to many (i.e: one person can have lots of start
and end dates)

Jason could have 01/01/01 to 01/06/01
02/06/01 to 31/12/01

Is it possible to two things

1. Work out the difference between the two to show on a subform next to
start and end dates
2. Add the totals together to show on the same form

the only snag is that I may not have an end date on the most recent because
it hasn’t ended yet. Therefore the end date would be today

Jason could have 01/01/01 to 01/06/01
02/06/01 to today

hope you can help

 

[Steve Schapel]

Moosh,

Do you mean you want to show the number of days between Start Date and
End Date. Well, you could put an unbound textbox on the form, and set
its Control Source property to either of these...
=[End Date]-[Start Date]
=DateDiff("d",[Start Date],[End Date])
However, to cater to the unfinished period, you could use the Nz()
function, as in...
=Nz([End Date],Date())-[Start Date]
Then, in the Form Footer, you can do another unbound textbox with this
Control Source...
=Sum(Nz([End Date],Date())-[Start Date])

Alternatively, in the query that the form is based on, you can do
something similar to make a calculated field in the query, something
like this in the Field row of a blank column in the query design grid...
Interval: Nz([End Date],Date())-[Start Date]
.... and then you can have a control on the form bound to this query
field, and then the Total textbox in the footer will be like this...
=Sum([Interval])

 

[eanyea]

Steve
The Query works great and works great in the form, thank you very much. But
the Unbound text box in the footer
=Sum([Interval])
doesn't want to work, any ideas
Regards
Moosh

Moosh,

Do you mean you want to show the number of days between Start Date and
End Date. Well, you could put an unbound textbox on the form, and set
its Control Source property to either of these...
=[End Date]-[Start Date]
=DateDiff("d",[Start Date],[End Date])
However, to cater to the unfinished period, you could use the Nz()
function, as in...
=Nz([End Date],Date())-[Start Date]
Then, in the Form Footer, you can do another unbound textbox with this
Control Source...
=Sum(Nz([End Date],Date())-[Start Date])

Alternatively, in the query that the form is based on, you can do
something similar to make a calculated field in the query, something
like this in the Field row of a blank column in the query design grid...
Interval: Nz([End Date],Date())-[Start Date]
.... and then you can have a control on the form bound to this query
field, and then the Total textbox in the footer will be like this...
=Sum([Interval])

--
Steve Schapel, Microsoft Access MVP

I have a database with two tables
1. table containing details of people
2. table containing two fields of which – Start Date and end date

These tables are joined one to many (i.e: one person can have lots of start
and end dates)

Jason could have 01/01/01 to 01/06/01
02/06/01 to 31/12/01

Is it possible to two things

1. Work out the difference between the two to show on a subform next to
start and end dates
2. Add the totals together to show on the same form

the only snag is that I may not have an end date on the most recent because
it hasn’t ended yet. Therefore the end date would be today

Jason could have 01/01/01 to 01/06/01
02/06/01 to today

hope you can help

 

[Tom Lake]

Steve
The Query works great and works great in the form, thank you very much.
But
the Unbound text box in the footer
=Sum([Interval])
doesn't want to work, any ideas
Regards
Moosh

You can't sum a calculated field. Try this:

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

Tom Lake

 

[Steve Schapel]

Moosh,

Should be fine. What actually happens? Did you use 'Interval' as the
alias in the query for the calculated field, i.e. as I suggested?...
Interval: Nz([End Date],Date())-[Start Date]
.... and did you then put a control on the form bound to this Interval
field? And what is the name of the control in the footer?

 

[eanyea]

Steve
Yes I called it interval in the query. But I don't know if this makes any
difference but in the form it's set up as the person details being the main
data and then a sub form from the query I set up with the events of the start
date end date and interval.
This works fine, I get the correct info:-
All person details with a sub form listing the events with each interval
total at the end of the sub form.
Therefore, just need to add all intervals for each person for the total
amount of days. Also does this total have to be done on a form or can you
place it in a query. I will need to run reports at a later date.
Also not sure about how to do the footer, I did it by creating a text box in
the footer, right clicking and selecting build event, then expression and
typing =sum(interval) and clicking ok.
Not sure is thats right
Regards
Moosh

Moosh,

Should be fine. What actually happens? Did you use 'Interval' as the
alias in the query for the calculated field, i.e. as I suggested?...
Interval: Nz([End Date],Date())-[Start Date]
.... and did you then put a control on the form bound to this Interval
field? And what is the name of the control in the footer?

--
Steve Schapel, Microsoft Access MVP

Steve
The Query works great and works great in the form, thank you very much. But
the Unbound text box in the footer
=Sum([Interval])
doesn't want to work, any ideas
Regards
Moosh

 

[Steve Schapel]

Moosh,

The textbox for the total days for each person will need to be in the
Foorter of the subform, not the footer of the main form. Put your
unbound textbox in there, and then you can right-click on the textbox
and select Properties from the pop-up menu. Find the Control Source
property, and in there you can enter like this...
=Sum([Interval])