List of years between two dates stored in the database

[jade1977]

I have a database that contains date spans and rents listed for each period. I am trying to build a report that shows the rent for each year listed in the database for each period. Ex:



Rent 2/1/2006 - 1/31/2008 $18,000,000

Rent 2/1/2008 - 1/31/2010 $25,000,000



What I need is for the report to list each year between 2/1/2008 - 1/31/2016 so that it should read:



2006 $18,000,000

2007 $18,000,000

2008 $24,405,479.45

2009 $25,000,000

2010 $25,000,000



I do not have an issue with figuring out the rent for each year, just how to list the years for each period.



If I need to give a better explanation, please let me know and I will try to give more details.



Any help to set me on the right path would be appreciated.

[jasonli]

If you can calculate the rent of each year, you also can get the number of the year. Don't think year number is a number, it is just a string, or ID of the year.

i.e., you will get a result as a Datatable, including FromDate, ToDate and Rent. FromDate would be the first day of the year, so get year part from FromDate as a string. Like:
CStr(Year ({vwDataSource.FromDate}), 0)

[jade1977]

I have already done that. The issue that I have is incrementing by one each year during a rent period and showing that on the report, as I listed below. The only time I would have to calculate the rent is during a split rent stream.