date calculations

[paulevans]

Hi
I would like to manipulate some date information. I have a start date field and an end date field and I would like to draw up a schedule for payment where the Acess takes the finished date and the start date to get total days appart then devides this by a value set by the operator. say If the operator wanted 6 paymes then my total days would be devided by 6 giving me the number of days to add on to the start date. Then with these dates a schedule is made up and the code I have tried looks something like this.

Dim valuation1 As Date
Dim valuation2 As Date
Dim valuation3 As Date
Dim valuation4 As Date
Dim valuation5 As Date
Dim valuation6 As Date
Dim valuation7 As Date
Dim valuation8 As Date
Dim valuation9 As Date
Dim valuation10 As Date
Dim valuation11 As Date
Dim valuation12 As Date
Dim startdate As Double
Dim findate As Double

Dim valcounter As Double


'retrieve start date and end date
startdate = CDbl(Me.Start_Date)
findate = CDbl(Me.Completion_Date)

'calculate evaluation periods
Dim valCount As Double
Dim valDays As Double

valCount = CDbl(Me.Valuation)


valDays = (startdate + ((findate - startdate) / valCount))


'Work on entering valuation dates

Do While valcounter < valCount

Select Case valcounter
Case "1"
Me.valuation1 = startdate
Case "2"
Me.valuation2 = startdate
Case "3"
Me.valuation3 = startdate
Case "4"
Me.valuation4 = startdate
Case "5"
Me.valuation5 = startdate
Case "6"
Me.valuation6 = startdate
Case "7"
Me.valuation7 = startdate
Case "8"
Me.valuation8 = startdate
Case "9"
Me.valuation9 = startdate
Case "10"
Me.valuation10 = startdate
Case "11"
Me.valuation11 = startdate
Case "12"
Me.valuation12 = startdate

End Select
valcounter = valcounter + 1
startdate = startdate + valDays

Loop
' this value is to test the interval period
Me.tdays = valDays

I am new to vb and so ths code is not going to be the best or anywhere near but any help would be great.

Thanks

 

[paulevans]

fixed

Solution to this problem was a simple bit of code re-write. Access dates handling is just like they say. Each day and part of is effectively a number and so calculations can be carried out in the same way as manipulating any other number. Just have to make sure your code is doing what you want and not as I did above. Having problems then leave alone and come back with a fresh look and there you go problem solved 15 mins.

Thanks for anyone who had a look and was going to try and help but solution now found.