Hi,

I have the following situation:

I have two fields: Start Date and Stop Date and I need to create a procedure to break them down and create records.

Start Date Stop Date
01/25/07 03/05/07

Results:
01/25/07 01/31/07
02/01/07 02/28/07
03/01/07 03/05/07

Thank you in advance for any help.

The following code should do what you require (make the appropriate substitutions for the highlighted Table and Field names):

Public Sub SplitDates(ByVal StartDate As Date, ByVal StopDate As Date)

Dim rs As DAO.RecordSet
Dim X As Long

Set rs = DBEngine(0)(0).OpenRecordSet("tblDestination", dbOpenDynaSet)

For X = Year(StartDate) * 12 + Month(StartDate) _
To Year(StopDate) * 12 + Month(StopDate)
rs.AddNew
If X = Year(StartDate) * 12 + Month(StartDate) Then
rs.Fields("Start Date") = StartDate
Else
rs.Fields("Start Date") = DateSerial(Int(X / 12), X Mod 12, 1)
End If
If X = Year(StopDate) * 12 + Month(StopDate) Then
rs.Fields("Stop Date") = StopDate
Else
rs.Fields("Stop Date") = DateSerial(Int(X / 12), (X Mod 12) + 1, 0)
End If
rs.Update
Next X

rs.Close
Set rs = Nothing

End Sub

Hi,

Thank you for sending me the answer to my problems. I am trying to run the code using a command button that will active the sub routine. What is the best way to do it? Considering that I need to duplicate the fields on the same table.

Thank you in advance,

Eddie