Query through VBA 2

[ItIsHardToProgram]

Hello, it is me again!

I am trying to apply something to the working and optimized query (thanks to PHV). I have a code that selects a employee number, in a form, and then tries to run the query through VBA, with an ADDED WHERE statement, the code goes as following:

SelectEmp = No_Emplo
        
        
        strSQL = "SELECT T.IdProjet, P.TempsPrevue, 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 TempsTotal, P.TempsPrevue-TempsTotal AS TempsRestant"
               
        strSQL = strSQL + " FROM (tblTimeSheet AS T INNER JOIN tblProjet AS P ON T.IdProjet=P.IdProjet) INNER JOIN" & _
                          " tblEmployer AS E ON T.IdEmployer=E.IdEmployer" & _
                          " GROUP BY T.IdProjet, P.TempsPrevue" & _
                          " WHERE (tblprojet.IdEmployerREsponsable=" & SelectEmp & ");"
        MsgBox (strSQL)
        
        DoCmd.RunSQL strSQL

VBA sends an error saying theres a syntax error (missing operator) in the WHERE statement. This statement was working with another query earlier, and it does not work with this one, am I using the WHERE statment the wrong way???

Thank you very much for your help!

Julien

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

 

[PHV]

The WHERE clause should be above the GROUP BY.

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

 

[ItIsHardToProgram]

Yes, I just figured that out as you mentioned it, but I get a new error:

Execution error 2342, it says an SQLexecution requires an SQL instruction...

Not quite sure what it all rhymes to.

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

 

[PHV]

The DoCmd.RunSQL method is for ACTION queries.
Use a recordset instead.

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

 

[ItIsHardToProgram]

Ok, I am far from being a pro with recordset, but you got me thinking, and I am wondering, from my access form where all this is located, is it possible to send the specific SQL in a excel spreadsheet, as a report?

If so, should I maybe ask somewhere else for these information, like VBA for applications?

Your help is much appreciated.


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

 

[ItIsHardToProgram]

Not only can it be done, but it is easy peasy, and quite understandable!

Read some FAQ, got working, and I made it work quite easily.

Thanks for your inputs PHV. HAve a good week end.

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