-
Query
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
-
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules