one record into multiple records based on from and to date

[June7]

You may have read my post before I finished editing. Review again.

[kwooten]

I am not sure what the DLookup expression should be.. How should I write it so it deletes the first record? I do understand how to write a DLookup just not in a way where it skips records

[June7]

DLookup cannot delete record, it can only provide info. Actually, don't even need the DLookup. Just run delete action on the EffDate.

 Dim rs As DAO.Recordset, intMons As Integer, x As Integer, dteDate As Date
 Set rs = CurrentDb.OpenRecordset("SELECT HSSN, EffDate, ToDate FROM tablename")
 With rs
     .MoveLast
     .MoveFirst
     While Not .EOF
         intMons = DateDiff("m", !EffDate, !ToDate) + 1
         dteDate = DateSerial(Year(!EffDate), Month(!EffDate), 1)
         For x = 1 To intMons
             If x = 1 Then CurrentDb.Execute "DELETE FROM EmpHistory WHERE HSSN='" & !HSSN & "' AND NewDate=#" & dteDate & "#"
             CurrentDb.Execute "INSERT INTO EmpHistory(HSSN, NewDate) VALUES('" & !HSSN & "', #" & dteDate & "#)"
             dteDate = DateAdd("m", 1, dteDate)
         Next
         .MoveNext
     Wend
 End With

[kwooten]

error: too few parameters. Expected 1.


Does it have to do with my INSERT INTO command: "INSERT INTO EmployeeHistory_tbl(HSSN, HSTATUS, NewDate) VALUES('" & !HSSN & "', '" & !HSTATUS & "', #" & dteDate & "#)"

[June7]

Is HSTATUS a field retrieved by the Recordset? I would think not. So what status do you want in each of these records?

"INSERT INTO EmployeeHistory_tbl(HSSN, HSTATUS, NewDate) VALUES('" & !HSSN & "', 'some value here', #" & dteDate & "#)"

[kwooten]

Is HSTATUS a field retrieved by the Recordset? I would think not. So what status do you want in each of these records?

"INSERT INTO EmployeeHistory_tbl(HSSN, HSTATUS, NewDate) VALUES('" & !HSSN & "', 'some value here', #" & dteDate & "#)"

I have that with !HSTATUS.

The debug is highlighting, as the error, CurrentDb.Execute "DELETE FROM EmpHistory WHERE HSSN='" & !HSSN & "' AND NewDate=#" & dteDate & "#"

Sub MonthlyEmployeeRS()

Dim rs As DAO.Recordset, intMons As Integer, x As Integer, dteDate As Date
Set rs = CurrentDb.OpenRecordset("SELECT HSSN, EffDate, ToDate, HSTATUS FROM EmpHistory")
With rs
    .MoveLast
    .MoveFirst
    While Not .EOF
        intMons = DateDiff("m", !EffDate, IIf(!ToDate > Date, Date, !ToDate)) + 1
        dteDate = DateSerial(Year(!EffDate), Month(!EffDate), 1)
        For x = 1 To intMons
            If x = 1 Then CurrentDb.Execute "DELETE FROM EmpHistory WHERE HSSN='" & !HSSN & "' AND NewDate=#" & dteDate & "#"
            CurrentDb.Execute "INSERT INTO EmployeeHistory_tbl(HSSN, HSTATUS, NewDate) VALUES('" & !HSSN & "', '" & !HSTATUS & "', #" & dteDate & "#)"
            dteDate = DateAdd("m", 1, dteDate)
        Next
        .MoveNext
    Wend
End With



End Sub

[June7]

You have two tables: EmpHistory, EmployeeHistory_tbl?

EmpHistory is source for recordset? EmpHistory has HSTATUS field?

EmployeeHistory_tbl is the table to insert new records? Then EmployeeHistory_tbl is also table for DELETE.

In spite of the table names in my example, use the appropriate table names from your db.

[kwooten]

yes that is all correct. The record source is EmpHistory and EmployeeHistory_tbl is where the data is being output to. EmpHistory has the status field along with the rest of the data.

[June7]

But your code shows the DELETE acting on EmpHistory, not EmployeeHistory_tbl.

[kwooten]

oh.. well that makes sense

[kwooten]

But your code shows the DELETE acting on EmpHistory, not EmployeeHistory_tbl.

that fixed it.. thank you so much.. I'd buy you a beer if I could