Long subquery

[diegomarino]

Hi, i have this query in vba

Sql = "SELECT Trattative.IDtrattativa, Candidati.IDcandidato, Candidati.Nome & "" "" & candidati.Cognome AS NomeCognome, Candidati.[RAL/Fatturato], Candidati.[penale economica], Trattative.[RAL richiesta], FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento percentuale], Candidati.[corrispettivo patto], FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo], [RAL richiesta]*5 AS [RAL 5 anni], [Penale economica]*2 AS [Penale * 2], [RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2], ([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS], [RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale], Candidati.[PTF trasferibile personale], Candidati.[Redditività ptf trasferibile], ([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo], [mint annuo]*5 AS [Mint 5 anni], Round([Costo totale]/[mint 5 anni],4) AS Costi, " & vbCrLf & _         "Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi, FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback " & vbCrLf & _
         "FROM Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID " & vbCrLf & _
         "WHERE (((Trattative.IDtrattativa)=443155041435185 " & vbCrLf & _
         "ORDER BY Candidati.Nome & "" "" & candidati.Cognome, Trattative.[RAL richiesta] DESC;"

this should be the source of another query, i set a string variable "sql" that is the first query

   Me.AnalisiOperazioneGrafico.RowSource = "SELECT IDtrattativa, NomeCognome, [Ricavi] AS Valore, ""Redditività"" AS CostiRicavi FROM (sql) UNION SELECT  IDtrattativa, NomeCognome, [costi], ""Costi"" FROM  (sql);"

it gives me error i think for the "", but i'm not able to insert the first query as source of the second query without using a variable, the first query it is too long

[June7]

Use an apostrophe instead of the doubled quote marks.

'Redditività'


`Costi`

[diegomarino]

It didn't work. Sorry, i made a mistake, first post edited

[diegomarino]

i tried not to use the "sql" variable and i write all the sql in the final query, but it says it's too long

"SELECT IDtrattativa, NomeCognome, [Ricavi] AS Valore, ""Redditività"" AS CostiRicavi FROM " & vbCrLf & _                                           "(SELECT Trattative.IDtrattativa, Candidati.IDcandidato, Candidati.Nome & "" "" & candidati.Cognome AS NomeCognome, Candidati.[RAL/Fatturato], Candidati.[penale economica], Trattative.[RAL richiesta], FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento percentuale], Candidati.[corrispettivo patto], FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo], [RAL richiesta]*5 AS [RAL 5 anni], [Penale economica]*2 AS [Penale * 2], [RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2], ([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS], [RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale], Candidati.[PTF trasferibile personale], Candidati.[Redditività ptf trasferibile], ([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo], [mint annuo]*5 AS [Mint 5 anni], Round([Costo totale]/[mint 5 anni],4) AS Costi, " & vbCrLf & _
                                           "Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi, FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback " & vbCrLf & _
                                           "FROM Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID " & vbCrLf & _
                                           "WHERE (((Trattative.IDtrattativa)=443155041435185 " & vbCrLf & _
                                           "ORDER BY Candidati.Nome & "" "" & candidati.Cognome, Trattative.[RAL richiesta] DESC;) " & vbCrLf & _
                                           "UNION SELECT  IDtrattativa, NomeCognome, [costi], ""Costi"" FROM " & vbCrLf & _
                                           "(SELECT Trattative.IDtrattativa, Candidati.IDcandidato, Candidati.Nome & "" "" & candidati.Cognome AS NomeCognome, Candidati.[RAL/Fatturato], Candidati.[penale economica], Trattative.[RAL richiesta], FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento percentuale], Candidati.[corrispettivo patto], FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo], [RAL richiesta]*5 AS [RAL 5 anni], [Penale economica]*2 AS [Penale * 2], [RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2], ([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS], [RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale], Candidati.[PTF trasferibile personale], Candidati.[Redditività ptf trasferibile], ([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo], [mint annuo]*5 AS [Mint 5 anni], Round([Costo totale]/[mint 5 anni],4) AS Costi, " & vbCrLf & _
                                           "Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi, FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback " & vbCrLf & _
                                           "FROM Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID " & vbCrLf & _
                                           "WHERE (((Trattative.IDtrattativa)=443155041435185 " & vbCrLf & _
                                           "ORDER BY Candidati.Nome & "" "" & candidati.Cognome, Trattative.[RAL richiesta] DESC;)"

[June7]

The vbCrLf's are not needed but shouldn't hurt.

Again, use apostrophes.

Don't end the first string with semi-colon.

Sql = "SELECT Trattative.IDtrattativa, Candidati.IDcandidato, Candidati.Nome & ' ' & candidati.Cognome AS NomeCognome, " & _
        "Candidati.[RAL/Fatturato], Candidati.[penale economica], Trattative.[RAL richiesta], " & _
        "FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento percentuale], Candidati.[corrispettivo patto], " & _
        "FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo], [RAL richiesta]*5 AS [RAL 5 anni], [Penale economica]*2 AS [Penale * 2], " & _
        "[RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2], " & _
        "([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS], " & _
        "[RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale], Candidati.[PTF trasferibile personale], Candidati.[Redditività ptf trasferibile], " & _
        "([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo], [mint annuo]*5 AS [Mint 5 anni], " & _
        "Round([Costo totale]/[mint 5 anni],4) AS Costi, Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi, FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback " & _
        "FROM Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID " & _
        "WHERE (((Trattative.IDtrattativa)=443155041435185 " & _
        "ORDER BY Candidati.Nome & ' ' & candidati.Cognome, Trattative.[RAL richiesta] DESC"

Also, need to concatenate the Sql variable.
FROM (" & Sql & ") UNION

Me.AnalisiOperazioneGrafico.RowSource = "SELECT IDtrattativa, NomeCognome, [Ricavi] AS Valore, 'Redditività' AS CostiRicavi FROM (" & Sql & ") " & _
                                          "UNION SELECT IDtrattativa, NomeCognome, [costi], 'Costi' FROM  (" & Sql & ");"

Could save the first sql as a query object then reference that query name in the second SQL.

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

[diegomarino]

thanks, i used query def but i hoped to get rid of that, so i was triyng a subquery.

you're right i made a lot of mistakes

i copied you solution but i have this error

[June7]

Translate to English please.

[Micron]

Agree - OP needs to translate, but since the number was there I did it this time:
The setting for this property is too long.

I'll bet anyone else's money that the data contains apostrophes (like O'Brien) or the sql contains an orphan delimiter and that's why the error. Check the data and output the sql to the immediate window (Debug.Print SQL) and post that.

[June7]

Not seeing cause for apostrophes to be an issue.

Do wonder if all the ID fields are number type. I am not able to enter 443155041435185 into a long integer field so is this generated by autonumber? A double field will take it and displays as scientific notation and the filter works.

[Micron]

Not seeing cause for apostrophes to be an issue.

Just posting what I found in my research as a solution for the error message.

[diegomarino]

no, it is a calculated field, a date/time with seconds.
i tried with a smaller number but i get the same error

[June7]

I am confused. IDtrattativa is a calculated date/time field?

[diegomarino]

yes, since i'm the only working on that and i need to use powerapps (linked to some excel files) too so i can't use an autonumber. so i set the id as date time with seconds, i can't literally make two records so fast.

sorry i missed the late night post, i didn't translate cause there were the number.

this is what i get in immediate wind.

SELECT Trattative.IDtrattativa, Candidati.IDcandidato, Candidati.Nome & ' ' & candidati.Cognome AS NomeCognome, Candidati.[RAL/Fatturato], Candidati.[penale economica], Trattative.[RAL richiesta], FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento percentuale], Candidati.[corrispettivo patto], FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo], [RAL richiesta]*5 AS [RAL 5 anni], [Penale economica]*2 AS [Penale * 2], [RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2], ([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS], [RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale], Candidati.[PTF trasferibile personale], Candidati.[Redditività ptf trasferibile], ([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo], [mint annuo]*5 AS [Mint 5 anni], Round([Costo totale]/[mint 5 anni],4) AS Costi, Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi, FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback FROM Candidati INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID WHERE (((Trattative.IDtrattativa)=100 ORDER BY Candidati.Nome & ' ' & candidati.Cognome, Trattative.[RAL richiesta] DESC

i can see it is cut. I don't know why

[Minty]

I have reformatted that to this;

SELECT Trattative.IDtrattativa,
	 Candidati.IDcandidato,
	 Candidati.Nome & ' ' & candidati.Cognome AS NomeCognome,
	 Candidati.[RAL/Fatturato],
	 Candidati.[penale economica],
	 Trattative.[RAL richiesta],
	 FormatPercent(([RAL richiesta]-[RAL/Fatturato])/[RAL/Fatturato],0) AS [Incremento percentuale],
	 Candidati.[corrispettivo patto],
	 FormatCurrency([ral richiesta]*15/100,0) AS [Corr patto nuovo],
	 [RAL richiesta]*5 AS [RAL 5 anni],
	 [Penale economica]*2 AS [Penale * 2],
	 [RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2] AS [RAL 5 anni + corr patto nuovo + Patto * 2],
	 ([RAL 5 anni]+([corr patto nuovo]*5)+[Penale * 2])*0.4 AS [Costo INPS],
	 [RAL 5 anni + corr patto nuovo + Patto * 2]+[Costo inps] AS [Costo totale],
	 Candidati.[PTF trasferibile personale],
	 Candidati.[Redditività ptf trasferibile],
	 ([ptf trasferibile personale]*1000000)*[redditività ptf trasferibile]/100 AS [Mint annuo],
	 [mint annuo]*5 AS [Mint 5 anni],
	 Round([Costo totale]/[mint 5 anni],4) AS Costi,
	 Round(1-[costo totale]/[mint 5 anni],4) AS Ricavi,
	 FormatNumber([Costo totale]/[mint annuo]*12,0) AS Payback 
FROM Candidati 
INNER JOIN Trattative ON Candidati.IDcandidato = Trattative.CandidatoID 
WHERE (((Trattative.IDtrattativa)=100 
ORDER BY Candidati.Nome & ' ' & candidati.Cognome, Trattative.[RAL richiesta] DESC

The only thing I can see that is possibly wrong is referring to an alias field in the query that the alias is created in - [RAL 5 anni]
SQL Server won't let you do that sometimes Access will.

If it is still truncating try adding to the sql string like this to break it into readable separate statements

StrSQL = " SELECT ....." 
StrSQL = StrSql & " FROM ...."
StrSQL = StrSql & " WHERE ...."

[davegri]

2 missing close parens?

WHERE (((Trattative.IDtrattativa)=100 
ORDER BY Candidati.Nome & ' ' & candidati.Cognome, Trattative.[RAL richiesta] DESC