VB 6 - Re: Run-Time error 3075

[Giftx]

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.
Giftx

[Twodogs]

When I ran your code inside vb6, it seemed to break a line in the middle of tblRequests, but still seemed to compile ok & give me a resulting string. Perhaps this may work (just added a couple of vbcrlf's)

Dim strSql As String
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 " & vbCrLf

strSql = strSql & "Union All " & vbCrLf _
& "  Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog2hrs, TblRequests.PrevProg2Hrs, TblRequests.TotalProg2hrs, 2 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel2 " & vbCrLf

strSql = strSql & "Union All " & vbCrLf _
& "  Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog3hrs, TblRequests.PrevProg3Hrs, TblRequests.TotalProg3hrs, 3 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel3 " & vbCrLf

strSql = strSql & "Union All " & vbCrLf _
& "  Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog4hrs, TblRequests.PrevProg4Hrs, TblRequests.TotalProg4hrs, 4 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel4 " & vbCrLf

strSql = strSql & "Union All " & vbCrLf _
& "  Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog5hrs, TblRequests.PrevProg5Hrs, TblRequests.TotalProg5hrs, 5 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel5 " & vbCrLf

strSql = strSql & "Union All " & vbCrLf _
& "  Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog6hrs, TblRequests.PrevProg6Hrs, TblRequests.TotalProg6hrs, 6 " _
& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel6 " & vbCrLf

strSql = strSql & ") As NewProgHrsTbl"
Debug.Print strSql

[dglienna]

Why are you using CALL for a SQL statement?

Private Function GetRecordset(ByVal pdteSearchDate As Date) As ADODB.Recordset
Dim strSQL      As String
Dim adoRec      As ADODB.Recordset
    strSQL = "SELECT * "
    strSQL = strSQL & "FROM AS_Createsched "
    strSQL = strSQL & "WHERE AS_Createsched.CSIN_dates = #" & Format$(pdteSearchDate, "d mmm yyyy") & "# "
    strSQL = strSQL & "AND HOUR(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "hh") & " "
    strSQL = strSQL & "AND MINUTE(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "nn") & " "
    Set adoRec = New ADODB.Recordset
    adoRec.Open strSQL, GetConnString, adOpenForwardOnly, adLockReadOnly
    Set GetRecordset = adoRec
    Set adoRec = Nothing
End Function

Private Function GetConnString() As String
    GetConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db4.mdb;Persist Security Info=False"
End Function

[Giftx]

Hello "dg".
I quite don't follow what your code example is saying.
This is my objective:

I have two input tables "TblRequests" and "TblPersonnel". I am creating a new table Called "NewProgHrsTbl", using some fields from "TblRequests" and a single field from "TblPersonnel".

Note:
In TblRequests, we have fields: Personnel1 and all the information related to it, Personnel2 and all the information related to it, Personnel3.......etc. all in the same row.

In the new table, I want Personnel1 and its information on one row, Personnel2 and its information on the next row, Personnel3 and its information on the next row.......etc.

This is why you see the concatenation of strSQL and the nested Select statements in my module.

That's my objective.

Thanks.
GiftX.

[dglienna]

You are using Execute, which doesn't return anything, and CALLing it like it's a subroutine, which it isn't. Does ANY sql statement work at that point? I think that's the first problem.

[WoF]

I don't think there is a difference in execution if you Call a sub or not.
If you call, VB expects the parentheses around the parameters.
I thought, in the end Call is only a convenience statement.