Hi all
sorry for the inconveniences, I just wanna get this syntax thing out of my back.
I modified my vb module and now I'm getting "Run-Time error 3075 syntax error(Missing operator) - query expression"
Then it displayed this line of code as the problem line:
'TblPersonnel.[Name] = TblRequests.Personnel1 Union All
Select TblRequests.ProjectID'.
Below is the complete module:
DbPath = "\\515opsisdallf00008\SHARED\PyrlApps\PTStest2\"
DbName = "PTS.mdb"
Set Db = OpenDatabase(DbPath & DbName)
strSQL = "Insert Into TblProgrammersHours (ProjectID,ProgrammerInitials, CurrProgHrs, PrevProgHrs, TotalProgHrs, Positions)"
strSQL = strSQL & " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog1hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1hrs, 1 As Position " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel1 "
strSQL = strSQL & "Union All " _
& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog2hrs, TblRequests.PrevProg2Hrs, TblRequests.TotalProg2hrs, 2 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel2 "
strSQL = strSQL & "Union All " _
& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog3hrs, TblRequests.PrevProg3Hrs, TblRequests.TotalProg3hrs, 3 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel3 "
strSQL = strSQL & "Union All " _
& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog4hrs, TblRequests.PrevProg4Hrs, TblRequests.TotalProg4hrs, 4 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel4 "
strSQL = strSQL & "Union All " _
& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog5hrs, TblRequests.PrevProg5Hrs, TblRequests.TotalProg5hrs, 5 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel5 "
strSQL = strSQL & "Union All " _
& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog6hrs, TblRequests.PrevProg6Hrs, TblRequests.TotalProg6hrs, 6 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel6 "
strSQL = strSQL & ") As NewProgHrsTbl"
Call Db.Execute(strSQL)
Thanks.
tgif