Code:
strSQL = "INSERT INTO dbo_PM_TEMP ( THRU_DATE, Process_Level_ID, Dept_ID, Position_ID, Position_Title, New_Position_Status, Job_Salary_Grade, Job_Code, Location_ID, FLSA_Status,"
strSQL = strSQL & " Expense_Account_Unit, Shift, Manager_Supervisor_Number, Manager_ID, Empl_ID, Last_Name, First_Name, Middle_Initial, Vacant_Position_Incumbent_ID,"
strSQL = strSQL & " Vacant_Position_Incumbent_Name, Adjusted_Hire_Date, Hourly_Rate, Assigned_FTE )"
strSQL = strSQL & " SELECT " & Chr(34) & dteNewThruDate & Chr(34) & " AS [Date], Left([Company],5) AS Process_Level_ID, Mid([Cost Center],6,4) AS Dept_ID, Sheet1.[Position ID],"
strSQL = strSQL & " IIf(IsNull([Job Profile]),"""",Right([Job Profile],Len([Job Profile])-6)) AS Position_Title, Sheet1.[Position Status],"
strSQL = strSQL & " IIf(IsNull([Default Compensation Grade]),"""",Mid([Default Compensation Grade],InStr([Default Compensation Grade],""-"")+1,2)) AS Job_Salary_Grade,"
strSQL = strSQL & " IIf(IsNull([Job Profile]),"""",Left([Job Profile],5)) AS Job_Code, Sheet1.[Location ID], IIf([Exempt?]=""yes"",""Exempt"",""Non-Exempt"") AS FLSA_Status,"
strSQL = strSQL & " Mid([Cost Center],6,4) & Mid([Cost Center],3,3) AS Expense_Account_Unit, IIf(IsNull([Work Shift]),0,Mid([Work Shift],7,1)) AS Shift,"
strSQL = strSQL & " Left([Position],10) AS Manager_Supervisor_Number,"
strSQL = strSQL & " IIf([Manager] Like ""*(On Leave)*"",Mid([Manager],InStr(InStr([Manager],""("")+1,[Manager],""("")+1,InStr(InStr(InStr([Manager],""("")+1,"
strSQL = strSQL & "[Manager],""(""),[Manager],"")"")-InStr(InStr([Manager],""("")+1,[Manager],""("")-1),Mid([Manager],InStr([Manager],""("")+1,InStr([Manager],"")"")"
strSQL = strSQL & "-InStr([Manager],""("")-1)) AS Manager_ID, Sheet1.[Employee ID], Sheet1.[Last Name], Sheet1.[First Name], Sheet1.[Middle Name], Sheet1.[Vacated Incumbent ID],"
strSQL = strSQL & " IIf(IsNull([Vacated Incumbent Name]),"""",Left([Vacated Incumbent Name],InStr([Vacated Incumbent Name],""("")-2)) AS Vacant_Position_Incumbent_Name, Sheet1.[Hire Date],"
strSQL = strSQL & " Sheet1.[Hourly Rate], Sheet1.FTE"
strSQL = strSQL & " FROM Sheet1"
CurrentDb.Execute strSQL