Show Billing Period

[Chiki]

Hi, I want help regarding showing the Billing Period for each of my customer
in the database.The Billing Period is Generally of 30 days for each
customer.I had many customers with different Billing Cycles for each of them.

My question to the forum,is How would i show the billing periods for each
customer basing on the first bill generated date.

For example, if A's bill is generated for the first time on 23/04/2010,with
the first billing period as 22/03/2010 - 22/04/2010.The Next billing cycle
is for 30 days.

How to calculate the Next billing cycle for each month for the same customer.

Please give me any ideas on working about this problem or provide me any
examples.

Thanks

 

[Sean Timmons]

So, you have a field that shows 23/04/2010, correct?

=DateAdd("m", 1, DATE)

Would add one to the month.

Not sure if you're using this in a Form, Query, Report, etc...

 

[Chiki]

Thanks for the Reply. Timmons....

Yes I m using the Fields on My Form.what exactly I want is how would i
display or show the Billing period on My form.

Coming to the Example,
As i said earlier if A's Bill Is generated for the first time on 23/04/2010
for the period 22/03/2010 to 22/04/2010.The Next month bill will also be
generated for the Same Dates i.e. 22/04/2010 to 22/05/2010. Like same for
every month...


Hope i had explained it in detail.Please help me.

 

[KenSheridan via AccessMonster.com]

This might not be a straightforward as you think. You need to step back a
little first and consider the tables you'll need for this. Firstly a
Customers table like this:

Customers
….CustomerID (primary key, e.g. an autonumber)
….CustomerName (text)
….BillingPeriod (integer number)
….more fields for address, phone etc.

Then a Bills table:

Bills
….CustomerID (foreign key long integer number)
….BillDate (date)
….BillFrom (date/time)
….BillTo (date/time)
….BillAmount

The primary key of this table is a composite one of CsutomerID and BillDate.

Within a form based on Customers include a subform based on Bills, linking
them on CustomerID. In the main parent form add a 'Create New Bill' button.
In its Click event procedure put code along these lines:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String
Dim strNextDateFrom as String
Dim strNextDateTo as String
Dim dtmlastDateFrom As Date
Dim dtmlastDateTo As Date
Dim intDays As Integer

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "CustomerID = " & Me.CustomerID
intDays = Me.BillingPeriod

' get latest BillFrom and Bill To dates for current customer,
' defaulting to 60 days prior to current date if no previous bill
dtmLastDateFrom = Nz(Dmax("DateFrom", "Bills", strCriteria),VBA.Date-60)
dtmLastDateTo = dtmLastDateFrom + intDays - 1

' format next dates from and to as date literals in ISO date format
strNextDateFrom = "#" & Format(dtmLastDateFrom+intDays,"yyyy-mm-dd") &
"#"
strNextDateTo = "#" & Format(dtmLastDateTo+intDays,"yyyy-mm-dd") & "#"

' insert row into table
strSQL = "INSERT INTO Bills(CustomerID, DateFrom, DateTo) " & _
"VALUES(" & Me.CustomerID & "," & strNextDateFrom & "," & _
strNextDateTo & ")"

cmd.CommandText = strSQL
cmd.Execute

' requery subform to show new row
Me.sfcBills.Requery

Where sfcBills is the name of the subform control housing the . NB this is
the name of the control in the parent form's Controls collection, not the
name of the underlying form object.

Note that you are using billing periods of a number of days the dates will
vary from month to month and, with a 30 day billing period for instance,
there will not be an exact 12 billing periods per calendar year. The dates
you have given as examples do not in fact constitute 30 day periods, and you
are overlapping the start and end dates of consecutive periods, whereas the
start date should be the day following the end date of the previous period.
The code above will avoid this but if you really want monthly billing periods
then you would need to store the number of months per customer in the
BillingPeriod column rather than days, and add months rather than days, for
which you'd need to use the DateAdd function rather than simple arithmetic.

Ken Sheridan
Stafford, England

 

[Chiki]

Thanks for the long code with the table design.I had a doubt regarding the
Field Billing Period (integer) whereas,it should be of Date data type.

My Next query is;how would i display the same dates with the months changed
for each month

 

[KenSheridan via AccessMonster.com]

The date/time data type cannot represent a time duration; it represents a
point in time by a 64 bit floating point number as an offset from 30 December
1899 00:00:00, with the integer part representing the days and the fractional
part the times of day. To represent a duration you need to do so in units of
an appropriate precision, in your case a day, so a 30 day billing period
would be represented by the integer number 30.

Your second question highlights the caveat I expressed over a 30 day period
as it suggests that you are really working in calendar months not days. The
length of a calendar month varies from 28 to 31 days of course, so adding
days would soon cause things to get out of step. If you are working in
calendar months then you should add months not days, so the appropriate
variable would now be declared as;

Dim intMonths As Integer

And assigned a value with:

intMonths = Me.BillingPeriod

BillingPeriod would now have a value in the table of 1 for one month rather
than 30 of course. You cannot use simple arithmetic to add months, so you'd
now use the DateAdd function:

' get latest BillFrom and Bill To dates for current customer,
' defaulting to 2 months prior to current date if no previous bill
dtmLastDateFrom = Nz(Dmax("DateFrom", "Bills", strCriteria), _
DateAdd("m",-2,VBA.Date))
dtmLastDateTo = DateAdd("m",intMonths,dtmLastDateFrom)-1

When the procedure is executed it will insert a new row into the Bills table
for the current customer, with new start and end dates one month (assuming
the billing period for the customer is 1 month) after the last row for that
customer in the table (or staring one month ago if no row for that customer
yet exists), so the months of the date will increase each time, but the day
of the month will be the same.

I've not been able to test the above code, of course, so you might need to do
some debugging when you implement it.

Ken Sheridan
Stafford, England