Find All Possible Expenses Within Time Frame with Replacements Until End Criteria

[jrub]

Hi All,



I am a huge NOOB here, so please bare with me

I have Query1 which I would like to add a record set called Expenditure Years to.

Basically, expenditures occur as iterative multiples until a criteria is reached.

For example, if the item was built in 2000, and has a life expectancy of 5 years, and has a life expectancy adjustment of 2 years (only is applied to first iteration), the multiples would list as below; until the base year+study period sum is reached.

Example: base year (1990) + study period years (30) = 2020
Expenditure 1 - Item 1 - 2007
Expenditure 2 - Item 1 - 2012
Expenditure 3 - Item 1 - 2017

it would stop here for Item 1 as 2017 + 5 >= 2020, BUT would continue to Item 2 and do it all over again listing possible expenditures.....

Expenditure 3 - Item 2 -.....
and so forth....


Can anyone come up with a VBA macro that accomplishes this? Any help is sincerely appreciated

Thank you

[June7]

You want query to retrieve records where base year + study period is < 2020?

SELECT * FROM tablename WHERE [Base] + GetStudyPeriod(life, adjustment) < 2020;

GetStudyPeriod would be a custom function called by the query where arguments of life and adjustment are passed to the function. Question is where do life and adjustment come from? Are they fields in the table? Show sample data set.

OR

Are you trying to create records? Queries can only show records for data that exists (UNION queries are something of an exception but doesn't apply to your situtation). You would have to write data to a table.