Help with Calculated Field

[Ather Mian]

I am using Acc 2K. I have a Contracts db. My query is:

Select

Contract #
Customer Name
Contract Amount
Effective Date
Expiration Date

From tContracts

Where Expiration Date > Date ()

I need three calculated fields:
Current Month Revenue = Contract Amount/Contract Length in months
Accumulated Revenue = Contract Amount/(Elapsed Month)
Future Revenue = Contract Amount-(Current Month + Accumulated Revenue)

I would greatly appreciate any help.

Thanks

Ather

 

[John Spencer (MVP)]

Select
Contract #,
Customer Name,
Contract Amount,
Effective Date,
Expiration Date
(ContractAmount/DateDiff("m",EffectiveDate,ExpirationDate) as CMRevenue,
(ContractAmount*(DateDiff("m",EffectiveDate,Date())/DateDiff("m",EffectiveDate,ExpirationDate))
as AccumRev
(ContractAmount -
(ContractAmount*(DateDiff("m",EffectiveDate,Date())/DateDiff("m",EffectiveDate,ExpirationDate))
as FutureRev
From tContracts
Where Expiration Date > Date ()

This won't be totally accurate since the formulas above are calculating based on
whole months even when only partial months have elapsed. For instance, the
DateDiff function will count 1 month for the period August 28 to Sept 2. You
might get better results calculating on the basis of 30 day months.