Quarterly Billing for Partial periods

[Lori]

Okay, I have created a new database to track sales agreements and generate
quarterly invoices. Each agreement will have at least one but possibly dozens
of "licenses" assigned to it. These licenses are issued in one year
increments (for the most part) and are billed at a predetermined rate
(usually $100 per month).

Now my question is, how would I set up and expression in the invoice query
so that if a license expires mid-quarter I can still generate the invoice for
the number of months billable during that quarter? As an example I have one
license that expires on 4/30/08. When the new invoice is generated at the end
of the second quarter, I need to bill this license for 1 month.

 

[Michel Walsh]

You bill for the less between the end of the quarter and the end of the
license:

EffectiveEndDate: iif( endOfQuarter < endOfLicense, endOfQuarter,
endOfLicense)


an, as example:

DateDiff( "m", StartOfQuarter, EffectiveEndDate )


if positive, could give the number of months to consider.





Hoping it may help,
Vanderghast, Access MVP

 

[Lori]

okay this helps with part of it but how do I have a query tell me how many
months are actually being billed?

 

[Michel Walsh]

I am not sure I understand that last question. The DATEDIFF("m", date1,
date2) supplies the number of month boundaries between the two given dates.
Is that is not supplying your number of months being billed?



Vanderghast, Access MVP