Prev: How can I stop my Access Calendar from displaying Arabic(?)
Next: Is this too many records
From: KenSheridan via AccessMonster.com on 8 May 2010 10:57
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

First  |  Prev  | 
Pages: 1 2
Prev: How can I stop my Access Calendar from displaying Arabic(?)
Next: Is this too many records