Vba Query - unexpected error 1

[ItIsHardToProgram]

Hello all! I have a query that is running fine in the Access query, but that is throwing me an error in the VBA environment.

This is the query:

SELECT Left(T.IdProjet,7) AS ProjetSommaire, Sum(E.SalaireHoraire*(T.Lundi+T.Mardi+T.Mercredi+T.Jeudi+T.Vendredi+T.Samedi+T.Dimanche+T.LundiRD+T.MardiRD+T.MercrediRD+T.JeudiRD+T.VendrediRD+T.SamediRD+T.DimancheRD)) AS [Honoraire utilisé]
FROM tblTimeSheet AS T INNER JOIN tblEmployer AS E ON T.IdEmployer=E.IdEmployer
GROUP BY T.IdProjet;

The VBA environment:

strSQLstrip = "SELECT  Left(T.IdProjet, 7) As ProjetSommaire, Sum(E.SalaireHoraire*(T.Lundi+T.Mardi+T.Mercredi+T.Jeudi+T.Vendredi+" & _
        "T.Samedi+T.Dimanche+T.LundiRD+T.MardiRD+T.MercrediRD+T.JeudiRD+T.VendrediRD+T.SamediRD+T.DimancheRD))" & _
        "AS [Honoraire utilisé]"
               
        strSQLstrip = strSQL + " FROM tblTimeSheet AS T INNER JOIN tblEmployer AS E ON T.IdEmployer=E.IdEmployer" & _
                          " GROUP BY T.IdProjet;"

When it fires:

[highlight]Set rst = dbs.OpenRecordset(strSQLstrip, dbOpenSnapshot)[/highlight]
    If Not rst.BOF Then rst.MoveFirst
      'For this template, the data must be placed in the appropriate cells of the spreadsheet

    Do

            
            appExcel.Workbooks(iWbk).Worksheets("Bilan").Cells(iRow, 1).Value = rst.Fields("ProjetSommaire")
            
            appExcel.Workbooks(iWbk).Worksheets("Bilan").Cells(iRow, 9).Value = rst.Fields("Honoraire utilisé")
            
            iRow = iRow + 1

        
 
        
        rst.MoveNext
    
    Loop Until rst.EOF

    rst.Close

Errors on the highlighted ligns.

It gives me the following error:

runtime error 3142
"character found at the end of sql statement."

I first thought of a semi-colon introduced by mistake in the statement, but nothing, then I looked everywhere to find another character that could have been introduced in between the routine and the strSQLstrip input, but theres nothing.

Maybe it is because strSQLstrip passes from a function to another? Even though I do the same thing with another SQL in the same function.

You should know this, it is the 2nd instance of a database query that I open in the same function, first instance being the following:

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rst.BOF Then rst.MoveFirst
      'For this template, the data must be placed in the appropriate cells of the spreadsheet

    Do
        For Each ws In appExcel.Workbooks(iWbk).Sheets(Array("JfSommaire", "MaSommaire", "MartinSommaire", "BrunoSommaire", "JimSommaire", "NancySommaire", "GuillaumeSommaire", "GB Sommaire"))
            For Each r In ws.Range(ws.Range("A4"), ws.Range("A4").End(xlDown))
                If ws.Cells(r.Row, 1) = rst.Fields("IDProjet") Then
                    ws.Cells(r.Row, 9).Value = rst.Fields("Honoraire utilisé")
                    bRecordUse = True
                End If
            Next
        Next
        
        If bRecordUse = False Then
            appExcel.Workbooks(iWbk).Worksheets("Absent").Cells(iRow, 1).Value = rst.Fields("IDProjet")
            appExcel.Workbooks(iWbk).Worksheets("Absent").Cells(iRow, 9).Value = rst.Fields("Honoraire utilisé")
            iRow = iRow + 1
        End If
        
        bRecordUse = False
        
        rst.MoveNext
    
    Loop Until rst.EOF

    rst.Close

Thanks for your help, don't hesitate to ask any question!


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Found a space at the first line between SELECT and Left(, thought that would fix it but it still renders me an error, In my understanding this run-time error happens when theres a character after the semi-colon.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[PHV]

strSQLstrip = strSQL[!]strip[/!] + " FROM tblTimeSheet AS T INNER JOIN tblEmployer AS E ON T.IdEmployer=E.IdEmployer" & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ItIsHardToProgram]

OH MY !

$BLUSH$

Thank you PHV for this cold shower.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Andrzejek]

Do yourself a favor and start using:

[blue]Debug.Print strSQLstrip[/blue]
Set rst = dbs.OpenRecordset([blue]strSQLstrip[/blue], dbOpenSnapshot)

Because you assume your SQL is correct, but sometimes is not. This way you can see what your DB gets, and when you see the SQL you may see your error

Have fun.

---- Andy

 

[ItIsHardToProgram]

Thanks for the tip! I will keep this in mind next time I have SQL in my VBA!

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.