Hello friends,
        I have on table with fields pid,startdate and enddate.
e.g
there is one record in above table as "111,21/1/2008,21/3/2008"

now when i pass start date and end date then i want records with 3 rows
as 111 21/1/2008
   111 21/2/2008
   111 21/3/2008
means one month interval
so please tell me using MS Access how to do this?

Thanks

Here is what I would to calculate these values. I am not sure what you are doing with them (report or new records etc) so not sure what other code you will need:

Dim dtStart As Date
Dim dtEnd As Date
Dim iPID As Integer
Dim dtMid As Date

iPID = Me.PID
dtStart = Me.StartDate
dtEnd = Me.EndDate

'Create recordset to store data
'Post record for Start Date

rs.AddNew
rs("PID") = iPID
rs("Dates") = dtStart
rs.Update

dtMid = dtStart

Do Until dtMid >= dtEnd
   dtMid = DateAdd("m", 1, dtMid)
     rs.AddNew
     rs("PID") = iPID
     rs("Dates") = dtMid
     rs.Update
Loop

rs.AddNew
rs("PID") = iPID
rs("Dates") = dtEnd
rs.Update

rs.Close

This will allow you to have n number of months each time.

I would do this instead, however:

pid,startdate and months
"111,21/1/2008,2"

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com