Dates in a report

[Calvin]

I am a database that is tracking customers and their contract amounts. In a
report I want to be able to basically take the date today Date() and then
take the date they were customers and multiply by how many years it's been
times their annual contract amount.

I hope this isn't as confusing as it sounds basically i want to do something
like this:

todays date 12/19/2007-12/19/2005=2 years as customer * $300 (annual
contract amount)=$600

I would want to put this in a report and I know how to use the control
source. Could someone please assist me in how I would do this.

Thank you much.

Calvin

 

[Douglas J. Steele]

Assuming there's a StartDate in the record that indicates when they became a
customer, DateDiff("yyyy", [StartDate], Date()) will return the number of
years between. However, note that it's very literal in determine how many
years are between two dates: DateDiff("yyyy", #2007-12-31#, #2008-01-01#)
will return 1 year, even though it's actually only 1 day. If you want a more
accurate difference, you need to use

DateDiff("yyyy", [StartDate], Date()) - IIf(Format([Date(), "mmdd") <
Format([StartDate], "mmdd"), 1, 0)

You can then multiply that result by 300 to get your desired value.