Problem with a Sql string in vba

[chacal]

Hi everyone,

Here is my problem: I'm trying to open a recordset with the following Sql string

Strsql = "SELECT DISTINCT Count([# dossier]) AS Nb_demandes, [Unité responsable] " & _
             "FROM (SELECT [table installations 2005].[# dossier], [table installations 2005].[Unité responsable] " & _
                   "FROM [table installations 2005] " & _
                   "WHERE (((Month([table installations 2005].[Date d'installation]))= " & MonthSelected & " AND ((Year([table installations 2005].[Date d'installation]))= " & YearSelected & ") AND (([table installations 2005].[Unité responsable]) Like '*ontréal*')))" & _
             "GROUP BY [Unité responsable]"

and when I try to execute my code, I get the error: "There is a syntax error in the From Clause."

I really don't know what is my error. Could someone help me please!

Thanks a lot!

 

[WindSailor]

What I do when developing SQL statements to be used for recordsets is to make a test query and pull all the data I want in the design mode only, and then switch to SQL view and insert your keywords. Adjust your statements and re-check your results. Only after I am satisfied with the results and the consistency of them, then I insert it into my recordset statement. For me that is the fastest way to ensure consistency and accuracy in my SQL statements especially if things are getting complicated. For that part Access will do most of the work and testing for you.

Access also seems to be very particlular in the structure when reading SQL statements for recordsets, especially in/for a .mde.

If I recall correctly, 'Distinct' is a 'read only' property or statement and is not updatable. Distinct Row is updatable.

Hope that helps in the future.

 

[Bat17]

And also the Flipside of what WindSailor says is to add a line after you build the string.

debug.print strsql

This will print the generated string to the debug window (ctrl-G) which you can copy and paste into a regular query in SQL View to see what is happening.

HTH

Peter

 

[namliam]

You are short 1 closing bracket....

Strsql = "SELECT DISTINCT Count([# dossier]) AS Nb_demandes, [Unité responsable] " & _
             "FROM (SELECT [table installations 2005].[# dossier], [table installations 2005].[Unité responsable] " & _
                   "FROM [table installations 2005] " & _
                   "WHERE (((Month([table installations 2005].[Date d'installation]))= " & MonthSelected & " AND ((Year([table installations 2005].[Date d'installation]))= " & YearSelected & ") AND (([table installations 2005].[Unité responsable]) Like '*ontréal*')))" & _
             ") GROUP BY [Unité responsable]"

It is to close the opening bracket in front of the Select....

Regards

 

[chacal]

Perfect!
You were right namliam, I was missing 1 closing bracket.
But now, when I run it, it does not give me any error but the recordset is empty.
But when I run the sql statement in a Access Query, it work fine...

I'll try to fix that!

 

[chacal]

Well, Now I have another problem with some inner join in another query!
When I run this sql code in a regular query, it works fine! but when I try to use it to open a recordset, I get the error : "Join expression not supported"

Here is my sql string in VBA:

        StrsqlSum = "SELECT Sum(T_Cable.CoutCable) AS CoutCable, Sum(T_Autre_Dep_HQ.CoutAutre_Dep_HQ) AS CoutAutreHQ, Sum(T_Temps_HQ.CoutTemps_HQ) AS CoutTempsHQ, " & _
                      "Sum(T_Diesel.CoutDiesel) AS CoutDiesel, Sum(T_Transport_Generatrice.CoutTransport_Generatrice) AS CoutGeneratrice, Sum(T_Location.CoutLocation) AS CoutLocation, " & _
                      "Sum(T_Transport_Transfo.CoutTransport_Transfo) AS CoutTransportTransfo, Sum(T_Transfo.CoutTransfo) AS CoutTransfo, Sum(T_Autre_Dep.CoutAutre_Dep) AS CoutAutreDep, " & _
                      "Sum(T_Temps_Ext.CoutTemps_Ext) AS CoutTempsExt, Sum(T_Electricien.CoutElectricien) AS CoutElectricien, [table installations 2005].[Unité responsable]" & _
                      "FROM (((((((((([table installations 2005] INNER JOIN T_Diesel ON [table installations 2005].[No] = T_Diesel.NoAutoInstallation) INNER JOIN T_Cable ON " & _
                      "[table installations 2005].[No] = T_Cable.NoAutoInstallation) INNER JOIN T_Autre_Dep_HQ ON [table installations 2005].[No] = T_Autre_Dep_HQ.NoAutoInstallation) " & _
                      "INNER JOIN T_Temps_HQ ON [table installations 2005].[No] = T_Temps_HQ.NoAutoInstallation) INNER JOIN T_Transport_Generatrice ON [table installations 2005].[No] = " & _
                      "T_Transport_Generatrice.NoAutoInstallation) INNER JOIN T_Location ON [table installations 2005].[No] = T_Location.NoAutoInstallation) INNER JOIN T_Transport_Transfo ON " & _
                      "[table installations 2005].[No] = T_Transport_Transfo.NoAutoInstallation) INNER JOIN T_Electricien ON [table installations 2005].[No] = T_Electricien.NoAutoInstallation) " & _
                      "INNER JOIN T_Temps_Ext ON [table installations 2005].[No] = T_Temps_Ext.NoAutoInstallation) INNER JOIN T_Autre_Dep ON [table installations 2005].[No] = " & _
                      "T_Autre_Dep.NoAutoInstallation) INNER JOIN T_Transfo ON [table installations 2005].[No] = T_Transfo.NoAutoInstallation" & _
                      "WHERE (((Year([table installations 2005].[Date d'installation]))= " & AnneeSelectionne & ") AND (([table installations 2005].[Unité responsable]) Like '*ontréal*'))" & _
                      "GROUP BY [table installations 2005].[Unité responsable]"

I never know what to change when I switch from query to VBA!
Thanks again in advance for your help!

 

[namliam]

Actually, changing from query to code... no need to change anything....

Just to put it there "properly"

This time your problem is here...
[table installations 2005].[Unité responsable]" & _
"FROM (((((((((([table installations 2005]

If you leave out the quotes you get:
].[Unité responsable]FROM

Which offcourse "is not supported" (OK Bad MS error... you get used to them)

Tip: Make your query more readable
PLUS: allways try to put a space on the end of the line AND the beginning Just in case you forget one, like you did here....

Just to make sure: Add a space like so:].[Unité responsable] " & _
And maybe even one in front of the FROM

Greetz

 

[chacal]

Great! It works finally!

Thanks to you namliam!
I appreciate!

 

[sgtSortor]

Thanks, I followed your advice and my sql statement runs beatifully now.