looping thru records in a table

[Guest]

I have a table with 3 fields (Emp_ID, Start_Date, End_Date) and i need to
loop thru the records and create another table with 2 fields (Emp_ID,
New_Date) where the New_Date is all the dates between Start_Date and End_Date.
Example:
This is the original table:
ID Start_Date End_Date
123 11/06/2007 13/06/2007
123 15/06/2007 16/06/2007

To become
ID New_Date
123 11/06/2007
123 12/06/2007
123 13/06/2007
123 15/06/2007
123 16/06/2007

I have already created a function to create the new table if i pass the 3
parmaters in the first table.

However, i need to loop thru each record of the 1st table and excute this
function in an automated manner.

Apperciate any help.
Thank you.
Marwan

 

[Guest]

Here is a function that should do what you want:

Function CreateNewDates()
'Read the min date for a specific Emp_ID
'The acquisition of this Emp_ID could also be in a recordset and
'loop through each record reading each Emp_ID
lngEmp_ID = 123
dtStartDate = DMin("Start_Date", "tblEmpDates", "Emp_ID = " & lngEmp_ID & "")
dtEndDate = DMax("End_Date", "tblEmpDates", "Emp_ID = " & lngEmp_ID & "")
'by adding one to the count of the day, we can include the last day
DayCnt = DateDiff("d", dtStartDate, dtEndDate) + 1
'create a record for each of the days between
'these dates staring with the startdate
For DayCntr = 1 To DayCnt
dtDayDate = DateAdd("d", DayCntr - 1, dtStartDate)
Set NewRs = CurrentDb.OpenRecordset("tblNewEmpDates")
NewRs.AddNew
NewRs.Fields("Emp_ID").Value = lngEmp_ID
NewRs.Fields("New_Date").Value = dtDayDate
NewRs.Update
Next DayCntr
NewRs.Close
Set NewRs = Nothing
End Function

Please note that this is designed to only process the records for a single
employee.

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com