error 2176 - too long

**diegomarino** · 03-02-2021, 05:55 PM

good to know, but how can i use it if has no name?

right now i use my query def as source from chart...

Originally Posted by Micron

FYI if you just create the query def with no name ("") it is truly temporary as it only exists in memory. When the procedure ends, it's gone. Not sure if that is of any interest.
Dim qdf As DAO.QueryDef

Dim strSql As String

strSql = "SELECT..."
Set qdf = CurrentDb.CreateQueryDef("",strSql)

**Micron** · 03-02-2021, 06:00 PM

I just went by the code where I saw the word 'temporary' and went off on a tangent I guess. I really haven't been following this one since post 5 and forgot it was about a chart. Oh well, save it for some other time I guess. I've used it before but it was probably only when creating a recordset from what I'd call volatile sql.

**diegomarino** · 03-02-2021, 06:04 PM

Originally Posted by Micron

I just went by the code where I saw the word 'temporary' and went off on a tangent I guess. I really haven't been following this one since post 5 and forgot it was about a chart. Oh well, save it for some other time I guess. I've used it before but it was probably only when creating a recordset from what I'd call volatile sql.

ok thanks, i was just wondering the use, it's great for recordset

**ssanfu** · 03-02-2021, 09:22 PM

From Post #7

Originally Posted by Minty

Use

strSql = " SELECT blah blah "
strSql = strSql & " More query goes in here "
strSql = strSql & " Etc etc "

From Post #9

Originally Posted by Welshgasman

You could try aliases as well?

Just for grins, I did both... (I don't want to go out into the cold and shovel snow!

currently 16F/ -9C)

Code:

  qry = "SELECT P.TrattativaID, Sum(P.Gestito) AS SommaDiGestito, IIf(IsNull([sommadigestito]),0,FormatNumber([sommadigestito]/[totale],3)) AS Valore, 'Gestito' as Investimento," 
  qry = qry & " Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT P.TrattativaID, Sum(P.Assicurativo) AS SommaDiAssicurativo, IIf(IsNull([sommadiassicurativo]),0,FormatNumber([sommadiassicurativo]/[totale],3)) AS Valore,
  qry = qry & " 'Assicurativo' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT P.TrattativaID, Sum(P.[Gestioni patrimoniali]) AS [SommaDiGestioni patrimoniali],"
  qry = qry & " IIf(IsNull([sommadigestioni patrimoniali]),0,FormatNumber([sommadigestioni patrimoniali]/[totale],3)) AS Valore, 'Gestioni patrimoniali' as Investimento,"
  qry = qry & " Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT Portafoglio.TrattativaID, Sum(P.Amministrato) AS SommaDiAmministrato, IIf(IsNull([sommadiamministrato]),0,FormatNumber([sommadiamministrato]/[totale],3)) AS Valore,"
  qry = qry & " 'Amministrato' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT P.TrattativaID, Sum(P.Certificati) AS SommaDiCertificati, IIf(IsNull([sommadicertificati]),0,FormatNumber([sommadicertificati]/[totale],3)) AS Valore,"
  qry = qry & " 'Certificati' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT P.TrattativaID, Sum(P.Liquidità) AS SommaDiLiquidità, IIf(IsNull([sommadiliquidità]),0,FormatNumber([sommadiliquidità]/[totale],3)) AS Valore,"
  qry = qry & " 'Liquidità' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333;"

Hope I didn't mess up and take out too much...

**diegomarino** · 03-03-2021, 03:06 AM

Originally Posted by ssanfu

From Post #7

From Post #9

Just for grins, I did both... (I don't want to go out into the cold and shovel snow!

currently 16F/ -9C)

Code:

  qry = "SELECT P.TrattativaID, Sum(P.Gestito) AS SommaDiGestito, IIf(IsNull([sommadigestito]),0,FormatNumber([sommadigestito]/[totale],3)) AS Valore, 'Gestito' as Investimento," 
  qry = qry & " Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT P.TrattativaID, Sum(P.Assicurativo) AS SommaDiAssicurativo, IIf(IsNull([sommadiassicurativo]),0,FormatNumber([sommadiassicurativo]/[totale],3)) AS Valore,
  qry = qry & " 'Assicurativo' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT P.TrattativaID, Sum(P.[Gestioni patrimoniali]) AS [SommaDiGestioni patrimoniali],"
  qry = qry & " IIf(IsNull([sommadigestioni patrimoniali]),0,FormatNumber([sommadigestioni patrimoniali]/[totale],3)) AS Valore, 'Gestioni patrimoniali' as Investimento,"
  qry = qry & " Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT Portafoglio.TrattativaID, Sum(P.Amministrato) AS SommaDiAmministrato, IIf(IsNull([sommadiamministrato]),0,FormatNumber([sommadiamministrato]/[totale],3)) AS Valore,"
  qry = qry & " 'Amministrato' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT P.TrattativaID, Sum(P.Certificati) AS SommaDiCertificati, IIf(IsNull([sommadicertificati]),0,FormatNumber([sommadicertificati]/[totale],3)) AS Valore,"
  qry = qry & " 'Certificati' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

  qry = qry & " UNION"
  qry = qry & " SELECT P.TrattativaID, Sum(P.Liquidità) AS SommaDiLiquidità, IIf(IsNull([sommadiliquidità]),0,FormatNumber([sommadiliquidità]/[totale],3)) AS Valore,"
  qry = qry & " 'Liquidità' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333;"

Hope I didn't mess up and take out too much...

i thought it worked....but always gives the same error..

**ssanfu** · 03-03-2021, 04:11 PM

1) What is the field type for Portafoglio.TrattativaID? Is it Text, Number/Long Integer, ????

2) Try this

Code:

  qry = "SELECT P.TrattativaID, Sum(P.Gestito) AS SommaDiGestito, IIf(IsNull([sommadigestito]),0,FormatNumber([sommadigestito]/[totale],3)) AS Valore, 'Gestito' as Investimento," 
  qry = qry & " Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Trattative as T INNER JOIN (Candidati as C INNER JOIN Portafoglio as P ON C.IDcandidato = P.CandidatoID) ON T.IDtrattativa = P.TrattativaID"
  qry = qry & " GROUP BY P.TrattativaID"
  qry = qry & " HAVING P.TrattativaID = 442244486458333"

Debug.Print qry

Does the SQL look correct?

2a) Create a new query and paste in the SQL from the Debug statement, then execute the query. What is the result?

**diegomarino** · 03-04-2021, 03:16 AM

thanks
i used this sql that's more correct (this was a old query with other stuff inside)

Code:

  qry = "SELECT Portafoglio.TrattativaID, Sum(P.Gestito) AS SommaDiGestito, IIf(IsNull([sommadigestito]),0,FormatNumber([sommadigestito]/[totale],3)) AS Valore, 'Gestito' as Investimento,"  qry = qry & " Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale"
  qry = qry & " FROM Portafoglio"
  qry = qry & " HAVING Portafoglio.TrattativaID = 442244486458333"

i made the debug print and i got

Code:

SELECT Portafoglio.TrattativaID, Sum(Gestito) AS SommaDiGestito, IIf(IsNull([sommadigestito]),0,FormatNumber([sommadigestito]/[totale],3)) AS Valore, 'Gestito' as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale FROM Portafoglio HAVING Portafoglio.TrattativaID = 442244486458333

made a query with this sql and it returns "the query doesn't include the expression 'trattativaid' as a part of aggregation function" (more or less, i had to translate)

ps TrattativaID" is a number

**ssanfu** · 03-04-2021, 06:04 PM

Originally Posted by diegomarino

..... TrattativaID" is a number

OK, but what type of number?? Integer, Long Integer, Single, Double???
I'm guessing it is a Double because the MAXIMUM Value for a LONG INTEGER Field 2,147,483,647 and you are filtering with 442,244,486,458,333 (too big for a Long).

Since you have posted a different query, is the whole UNION query changing?

You got the error because you are using a TOTALS query. In the query design view, for the field Portafoglio.TrattativaID, the Totals row is undefined.
I modified your newest query to this

Code:

SELECT Portafoglio.TrattativaID, Sum(Portafoglio.Gestito) AS SommaDiGestito, IIf(IsNull([sommadigestito]),0,FormatNumber([sommadigestito]/[totale],3)) AS Valore, 'Gestito' AS Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale
FROM Portafoglio
GROUP BY Portafoglio.TrattativaID, 'Gestito'
HAVING (((Portafoglio.TrattativaID)=442244486458333));

You should read up on "Totals query"....

I would suggest getting a query to return the results you want, then build the UNION query or Post your dB. It is hard to trouble shoot a query when you don't know the dB design and you have no data.

BTW, you should not use spaces in object names (gestioni patrimoniali).
Also, "Valore" (Value) is a reserved word in Access and shouldn't be used for object names.

**diegomarino** · 03-06-2021, 12:41 PM

thanks, sure it is a total query so this is the problem

TrattativaID is a double

good advice on spaces in fields...but i made it when i didn't know it and now i don't want to change everywhere this field is mentioned in vba

i tried the new code

Code:

SELECT Portafoglio.TrattativaID, Sum(Portafoglio.Gestito) AS SommaDiGestito, IIf(IsNull([sommadigestito]),0,FormatNumber([sommadigestito]/[totale],3)) AS Valore, 'Gestito' AS Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS TotaleFROM Portafoglio
GROUP BY Portafoglio.TrattativaID, 'Gestito' HAVING (((Portafoglio.TrattativaID)=442244486458333));

got the debug print and made a query on that debud print.
i have a the correct result, but i tried then the unions

Code:

'qry = "SELECT Portafoglio.TrattativaID, Sum(Portafoglio.Gestito) AS SommaDiGestito, IIf(IsNull([sommadigestito]),0,FormatNumber([sommadigestito]/[totale],3)) AS Valore, 'Gestito' AS Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale FROM Portafoglio GROUP BY Portafoglio.TrattativaID, 'Gestito'HAVING (((Portafoglio.TrattativaID)=442244486458333)); "'qry = qry & "UNION SELECT Portafoglio.TrattativaID, Sum(Portafoglio.Assicurativo) AS SommaDiAssicurativo, IIf(IsNull([SommaDiAssicurativo]),0,FormatNumber([SommaDiAssicurativo]/[totale],3)) AS Valore, 'Assicurativo' AS Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale FROM Portafoglio GROUP BY Portafoglio.TrattativaID, 'Gestito'HAVING (((Portafoglio.TrattativaID)=442244486458333)); "
'qry = qry & "UNION SELECT Portafoglio.TrattativaID, Sum(Portafoglio.[Gestioni Patrimoniali]) AS [SommaDiGestioni patrimoniali], IIf(IsNull([SommaDiGestioni patrimoniali]),0,FormatNumber([SommaDiGestioni patrimoniali]/[totale],3)) AS Valore, 'Gestioni patrimoniali' AS Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale FROM Portafoglio GROUP BY Portafoglio.TrattativaID, 'Gestito'HAVING (((Portafoglio.TrattativaID)=442244486458333)); "
'qry = qry & "UNION SELECT Portafoglio.TrattativaID, Sum(Portafoglio.Amministrato) AS SommaDiAmministrato, IIf(IsNull([SommaDiAmministrato]),0,FormatNumber([SommaDiAmministrato]/[totale],3)) AS Valore, 'Amministrato' AS Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale FROM Portafoglio GROUP BY Portafoglio.TrattativaID, 'Gestito'HAVING (((Portafoglio.TrattativaID)=442244486458333)); "
'qry = qry & "UNION SELECT Portafoglio.TrattativaID, Sum(Portafoglio.Certificati) AS SommaDiCertificati, IIf(IsNull([SommaDiCertificati]),0,FormatNumber([SommaDiCertificati]/[totale],3)) AS Valore, 'Certificati' AS Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale FROM Portafoglio GROUP BY Portafoglio.TrattativaID, 'Gestito'HAVING (((Portafoglio.TrattativaID)=442244486458333)); "
'qry = qry & "UNION SELECT Portafoglio.TrattativaID, Sum(Portafoglio.Liquidità) AS SommaDiLiquidità, IIf(IsNull([SommaDiLiquidità]),0,FormatNumber([SommaDiLiquidità]/[totale],3)) AS Valore, 'Liquidità' AS Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidità])) AS Totale FROM Portafoglio GROUP BY Portafoglio.TrattativaID, 'Gestito'HAVING (((Portafoglio.TrattativaID)=442244486458333));"

and i got the same error, string too long.
I think the only way is through querdy def...

error 2176 - too long

Thread Tools

Similar Threads

RT error 2176 "The setting for this property is too long."

Access Error - String returned by builder was too long - the results will be truncated.

edit control source in VBA error "The Text Is Too Long to Be Edited"

Query Error? - "The text is to long to be edited"

Text too long, error message

Posting Permissions