Report Filtered on a Calculated Number

[Tiggaa]

Hi, sorry if this is in the incorrect segment, I was not sure if it should be in here or in queries.

Scenario: I have a table that has mutiple fields, two of which are "purchase date" and "life expectancy", the table lists all the equipment we have purchased.

I need to produce a report that list by year what equipment needs to be replaced and how much it costs to replace it (using the purchase price).

What I Would Like: I would like someone to give me a pointer as to where I should place the filter / make the calculation. Shoould I make a temperary (calculated) field.

Thank you for your assitance.

Tiggaa

 

[dhookom]

I would calculate the replacement date in the query and then use the WHERE CONDITION argument of the DoCmd.OpenReport method to filter the report.

Duane
Hook'D on Access
MS Access MVP

 

[JJSRich]

Write it in to your query.

You would want something akin to (pseudocode)

select
(purchase_date + life_expectancy) as date_of_replacement
, purchase_price
from equipment
left join purchase_dates
etc.

then let your report chop up in to years

Shouldn't be too bad if you've nromalized your data, but hey, this is Access, no one does proper data structure in Access