Query

[idareu]

Hi,



I have a table that has different accounts. The fields included are

Date Entered
Service Start Date
Account
Cycle
Monthly Charge

I need a formula to calculate backbilling charges depending on when the service was entered in service. If the date the service was entered was 5/4/2006 and the cycle date is 13. I would need to calculate backbilling charges from Service start date until 5/13/06 which is the next cycle date after the service was entered. Any ideas on what expression I can use to get this information automated?

Thanks

YDR

[matthewspatrick]

To simplify this, I will assume that the first partial month will be based on a 30-day period.

Try something like this to get the charges for any given cycle date:

SELECT Account, IIf(([Enter billing date] - [ServiceStartDate]) BETWEEN 1 AND 29, [MonthlyCharge] * ([Enter cycle date] - [ServiceStartDate]) / 30, [MonthlyCharge]) AS BillAmount
FROM YourTable
WHERE Day([Cycle]) = Day([Enter billing date]) AND ServiceStartDate < [Enter billing date]