Prev: How can I stop my Access Calendar from displaying Arabic(?)
Next: Is this too many records
From: Chiki on 30 Apr 2010 07:26
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



From: Sean Timmons on 30 Apr 2010 08:54
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" wrote:

> 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
>
>
>
From: Chiki on 1 May 2010 00:11
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.

From: KenSheridan via AccessMonster.com on 1 May 2010 14:04
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 wrote:
>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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

From: Chiki on 8 May 2010 05:48
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
 |  Next  |  Last
Pages: 1 2
Prev: How can I stop my Access Calendar from displaying Arabic(?)
Next: Is this too many records