Billing database calculation

[Guest]

Hello,

I am creating a billing database in (Access 2003) wherein a customer will be
billed yearly for services depending on the start of the service.

If the service activation date is 5/14/2005, I want to create a calculation
that bills the customer the yearly rate on 5/2006, 5/2007, etc.

I want to run statements monthly.

The yearly rate will never change.

If anyone has any ideas on how to set this up I would greatly appriciate
your insight.

Thanks.

 

[Ken Snell [MVP]]

Not sure how you want to do this in your database, but look at the DateAdd
function to see how you can add one year to a date and get a new date. You
then can use that date for the "yearly" anniversary?

If you can post more details, we might be able to give more specific
suggestions.

 

[Guest]

Thank you for the response.

I am using the DateAdd onto the year but this still poses problems:

Right now I am saying if [activation_month] = [current_month] then bill the
yearly rate. In this case, it bills every time I open the database during
that month. Also, I can't calculate it for the exact day because there are
days where the database will not be open and then the person will not be
billed at all or if it is opened twice in one day the person will be billed
two times.

Is there another way to do this?

Thank you.

 

[Ken Snell [MVP]]

If you want to "control" when and if the database does the billing, you'll
need to use a table into which you write a value that tells the database if
it's already done the desired billing or not. I don't know anything about
your database's structure, so I can't suggest specific ideas, but this
generic concept should be modifiable for your use.

It's generally not a good idea to rely on whether a user opens a database or
not if you want to do daily activities (e.g., billings). You may need to
rethink how you initiate the billings... perhaps use a Windows Scheduler
setup to open a database that does the billing, and then closes itself down
when done for the day?
--

Ken Snell
<MS ACCESS MVP>

Thank you for the response.

I am using the DateAdd onto the year but this still poses problems:

Right now I am saying if [activation_month] = [current_month] then bill
the
yearly rate. In this case, it bills every time I open the database during
that month. Also, I can't calculate it for the exact day because there are
days where the database will not be open and then the person will not be
billed at all or if it is opened twice in one day the person will be
billed
two times.

Is there another way to do this?

Thank you.

Not sure how you want to do this in your database, but look at the
DateAdd
function to see how you can add one year to a date and get a new date.
You
then can use that date for the "yearly" anniversary?

If you can post more details, we might be able to give more specific
suggestions.