If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Show Billing Period
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 |
#2
|
|||
|
|||
Show Billing Period
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 |
#3
|
|||
|
|||
Show Billing Period
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. |
#4
|
|||
|
|||
Show Billing Period
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/For...arted/201005/1 |
#5
|
|||
|
|||
Show Billing Period
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 |
#6
|
|||
|
|||
Show Billing Period
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 Chiki wrote: 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 -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|