subtracting months for two dates; syntax for query requested

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using access 2000 and windows xp home.

I'm trying to figure out rent arrears by months for the coop board.
Example, startdate is 10/1/05; enddate is 3/14/06. Rent is $1200. I want
the calculation to be something like this

Arrears: [startdate]-[enddate]x1200. I don't know how to make the result
equal a nice round six months, so I can multiply it by the monthly rents.
Some months have 30 or 31 days, and there is alwas February. SQL or other
programming language is unfathomable to me. Suggested simply syntax would be
great even if not the most elegant. In most, but not all instances, I'll use
date() as the enddate. Thanks in advance for any help that may b provided.
 
It looks as if you need to use the DateDiff function.

(DateDiff("m",[StartDate],[EndDate]) +1) * RentRate

DateDiff counts the months between two dates.
Jan to Feb = 1 month
But you want to count the last month also, so add 1.
Now multiply that times the Rent.

If you want to use Date() then put that in place of [EndDate].
 
Back
Top