Function Code_Fragment_7_23()

Dim dbs As Database
Dim Part_A, Part_B, Part_C, Part_D, Part_E, Part_F, Part_G, Part_H As String
Dim rst1, rst2 As Recordset


Set dbs = CurrentDb()



 
Part_B = "INSERT INTO INCUMBENTS ( SSN, PCN, START_DATE, END_DATE )" _
       & "SELECT " & Chr(34) & "111223333" & Chr(34) & ", PCN, #01/01/98#, END_DATE " _
       & "FROM INCUMBENTS " _
       & "WHERE SSN = " & Chr(34) & "112223333" & Chr(34) & " " _
       & "AND START_DATE < #01/01/1998# " _
       & "AND END_DATE > #01/01/1998# "

Part_A = "INSERT INTO INCUMBENTS ( SSN, PCN, START_DATE, END_DATE ) " _
       & "SELECT " & Chr(34) & "111223333" & Chr(34) & ", PCN, START_DATE, #01/01/1997# " _
       & "FROM INCUMBENTS " _
       & "WHERE SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
       & "AND START_DATE < #01/01/1997# " _
       & "AND END_DATE > #01/01/1997# "

Set rst1 = dbs.OpenRecordset("SELECT PCN " _
                           & "FROM POSITIONS, JOB_TITLES " _
                           & "WHERE POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE " _
                           & "  AND JOB_TITLE = " & Chr(34) & "Director, Computer Center" & Chr(34) & " " _
                           & "  AND POSITIONS.START_DATE < #01/01/1997# " _
                           & "  AND POSITIONS.END_DATE > #01/01/1997# ")
rst1.MoveFirst

Part_C = "UPDATE INCUMBENTS " _
       & "SET PCN = " & rst1!pcn & " " _
       & "WHERE SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
       & "AND START_DATE < #01/01/1998# " _
       & "AND END_DATE > #01/01/1997# "


Part_D = "INSERT INTO INCUMBENTS (SSN, PCN, START_DATE, END_DATE) " _
       & "SELECT " & Chr(34) & "111223333" & Chr(34) & ", " _
       & "POSITIONS.PCN, POSITIONS.START_DATE, POSITIONS.END_DATE " _
       & "FROM INCUMBENTS, POSITIONS, JOB_TITLES " _
       & "WHERE SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
       & "AND INCUMBENTS.START_DATE <= #01/01/1998# " _
       & "AND INCUMBENTS.END_DATE > #01/01/1998# " _
       & "AND POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE " _
       & "AND JOB_TITLE = " & Chr(34) & "Director, Computer Center" & Chr(34) & " " _
       & "AND #01/01/1997# <= POSITIONS.START_DATE " _
       & "AND INCUMBENTS.START_DATE <= POSITIONS.START_DATE " _
       & "AND POSITIONS.END_DATE < #01/01/1998# " _
       & "AND POSITIONS.END_DATE < INCUMBENTS.END_DATE "
          
Part_E = "INSERT INTO INCUMBENTS (SSN,PCN,START_DATE, END_DATE)" _
       & "SELECT " & Chr(34) & "111223333" & Chr(34) & ", " _
       & "POSITIONS.PCN, POSITIONS.START_DATE , #01/01/1998# " _
       & "FROM INCUMBENTS, POSITIONS, JOB_TITLES " _
       & "WHERE SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
       & "AND INCUMBENTS.START_DATE <= #01/01/1998# " _
       & "AND INCUMBENTS.END_DATE > #01/01/1998# " _
       & "AND POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE " _
       & "AND JOB_TITLE = " & Chr(34) & "Director, Computer Center" & Chr(34) & " " _
       & "AND #01/01/1997# <= POSITIONS.START_DATE " _
       & "AND #01/01/1998# < POSITIONS.END_DATE "
                    
Part_F = "UPDATE INCUMBENTS " _
       & "Set START_DATE = #01/01/1997# " _
       & "WHERE SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
       & "AND START_DATE < #01/01/1997# " _
       & "AND END_DATE > #01/01/1997# "

Part_G = "Update INCUMBENTS " _
       & "Set END_DATE = #01/01/1998# " _
       & "WHERE SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
       & "AND START_DATE < #01/01/1998# " _
       & "AND END_DATE > #01/01/1998# " _
       & "AND NOT EXISTS (SELECT * " _
       & "                FROM INCUMBENTS AS I2 " _
       & "                WHERE INCUMBENTS.SSN = I2.SSN " _
       & "                AND INCUMBENTS.PCN = I2.PCN " _
       & "                AND INCUMBENTS.START_DATE < I2.END_DATE " _
       & "                AND I2.START_DATE < INCUMBENTS.END_DATE) "


Set rst2 = dbs.OpenRecordset("SELECT MIN(I2.START_DATE) AS S_DATE " _
                           & "FROM INCUMBENTS,INCUMBENTS AS I2 " _
                           & "WHERE INCUMBENTS.SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
                           & "  AND I2.SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
                           & "  AND INCUMBENTS.PCN <> I2.PCN " _
                           & "  AND INCUMBENTS.START_DATE < I2.START_DATE ")

                           
rst2.MoveFirst

Part_H = "Update INCUMBENTS " _
       & "SET END_DATE = #" & rst2!S_DATE & "# " _
       & "WHERE SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
       & "  AND START_DATE < #01/01/1998# " _
       & "  AND END_DATE > #01/01/1998# " _
       & "  AND EXISTS (SELECT * " _
       & "              FROM INCUMBENTS AS I2 " _
       & "              WHERE INCUMBENTS.SSN = I2.SSN " _
       & "                AND INCUMBENTS.PCN <> I2.PCN " _
       & "                AND INCUMBENTS.START_DATE < I2.END_DATE " _
       & "                AND I2.START_DATE < INCUMBENTS.END_DATE) "

dbs.Execute (Part_A)
dbs.Execute (Part_B)
dbs.Execute (Part_C)
dbs.Execute (Part_D)
dbs.Execute (Part_E)
dbs.Execute (Part_F)
dbs.Execute (Part_G)
dbs.Execute (Part_H)


End Function



