error 2176 - too long

**diegomarino** · 03-02-2021, 11:10 AM

hi, i have a chart that the rowsource is this sql:

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 Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID
GROUP BY Portafoglio.TrattativaID 
HAVING (((Portafoglio.TrattativaID)=442244486458333)) 
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 Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID 
GROUP BY Portafoglio.TrattativaID
HAVING (((Portafoglio.TrattativaID)=442244486458333)) 
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 Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID
GROUP BY Portafoglio.TrattativaID
HAVING (((Portafoglio.TrattativaID)=442244486458333)) 
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 Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID
GROUP BY Portafoglio.TrattativaID 
HAVING (((Portafoglio.TrattativaID)=442244486458333)) 
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 Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID 
GROUP BY Portafoglio.TrattativaID
HAVING (((Portafoglio.TrattativaID)=442244486458333)) 
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 Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID 
GROUP BY Portafoglio.TrattativaID 
HAVING (((Portafoglio.TrattativaID)=442244486458333));

when i open the report containing the chart i got the "error 2176" cause the setting is too long

i tried to concatenate pieces of sql but got the same problem
as stand alone query it works.

someone can help me?

**ranman256** · 03-02-2021, 11:17 AM

put each segment into its own query , then the union is:
select * from Q1
union

select * from Q2
union
select * from Q3

**Micron** · 03-02-2021, 11:17 AM

Make the rowsource the query name and not a War and Peace length of sql? That's about 3,700 characters.

**diegomarino** · 03-02-2021, 11:42 AM

this do not work unfortunely

**Micron** · 03-02-2021, 11:50 AM

My mind reading hat is in the repair shop. "this do not work unfortunely" is in response to whom?

**diegomarino** · 03-02-2021, 11:54 AM

with "own query" what do you mean? to use a query def?

ps sorry i wrongly pressed "reply" instead of "reply with quote"

i'm triyng not to have query in my objects to make the database smaller, but i like to set recordsource and rowsource to the sql code

**Minty** · 03-02-2021, 01:00 PM

If you are concatenating that using

& _

You can't exceed 15 or 16 lines from memory.

Use

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

However query objects take up tiny amounts of space so I don't understand your concern?

**diegomarino** · 03-02-2021, 01:43 PM

thank you very much, i made this

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 " & vbCrLf & _         "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
         "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
         "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 " & vbCrLf & _
                "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
                "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
                "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 " & vbCrLf & _
               "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
               "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
               "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 " & vbCrLf & _
'               "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
'               "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
'               "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 " & vbCrLf & _
'               "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
'               "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
'               "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 " & vbCrLf & _
'               "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
'               "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
'               "HAVING (((Portafoglio.TrattativaID)=442244486458333));"

but it gets only the not commented parts, the whole thing give me the same error..

**Welshgasman** · 03-02-2021, 01:51 PM

Why can you not try Ranman256's suggestion?

You could try aliases as well?

Also I personally do not understand

Code:

SELECT Portafoglio.TrattativaID ... FROM Trattative

normally that would be

Code:

SELECT Portafoglio.TrattativaID ... FROM Portafoglio

**diegomarino** · 03-02-2021, 02:13 PM

Originally Posted by Welshgasman

Why can you not try Ranman256's suggestion?

i didn't understand what "q1","q2" is...if a string variable or something else

[QUOTE=You could try aliases as well?[/QUOTE]

aliases?

Originally Posted by Welshgasman

Also I personally do not understand

Code:

SELECT Portafoglio.TrattativaID ... FROM Trattative

normally that would be

Code:

SELECT Portafoglio.TrattativaID ... FROM Portafoglio

i give a better look

**accesstos** · 03-02-2021, 04:19 PM

Originally Posted by Micron

Make the rowsource the query name and not a War and Peace length of sql?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~

Diego,

Leave the Record Source blank and put the code below in report's module:

Code:

Option Compare Database
Option Explicit

Private Const cstrQName As String = "qryTemp" 'The name of the temporary query
'

Private Sub Report_Open(Cancel As Integer)
    Dim qdef As DAO.QueryDef    'The temporary query
    
    On Error Resume Next
    With CurrentDb
        'Try to delete the temp query
        .QueryDefs.Delete cstrQName
        'Create the temp query
        Set qdef = .CreateQueryDef(cstrQName, MyRecordSource)
    End With
    'Set the recordsource of the report to the temp query
    Me.RecordSource = cstrQName
End Sub

Private Sub Report_Close()
    'Delete the temp query
    On Error Resume Next
    CurrentDb.QueryDefs.Delete cstrQName
End Sub

Private Function MyRecordSource() As String
    Dim qry As String

    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]![liquidita])) AS Totale " & vbCrLf & "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
          "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
          "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]![liquidita])) AS Totale " & vbCrLf & _
          "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
          "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
          "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]![liquidita])) AS Totale " & vbCrLf & _
          "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
          "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
          "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]![liquidita])) AS Totale " & vbCrLf & _
          "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
          "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
          "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]![liquidita])) AS Totale " & vbCrLf & _
          "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
          "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
          "HAVING (((Portafoglio.TrattativaID)=442244486458333)) "
    qry = qry & "UNION SELECT Portafoglio.TrattativaID, Sum(Portafoglio.Liquidita) AS SommaDiLiquidita, IIf(IsNull([sommadiliquidita]),0,FormatNumber([sommadiliquidita]/[totale],3)) AS Valore, ""Liquidita"" as Investimento, Sum(Nz([gestito])+Nz([assicurativo])+Nz([gestioni patrimoniali])+Nz([amministrato])+Nz([certificati])+Nz([portafoglio]![liquidita])) AS Totale " & vbCrLf & _
          "FROM Trattative INNER JOIN (Candidati INNER JOIN Portafoglio ON Candidati.IDcandidato = Portafoglio.CandidatoID) ON Trattative.IDtrattativa = Portafoglio.TrattativaID " & vbCrLf & _
          "GROUP BY Portafoglio.TrattativaID " & vbCrLf & _
          "HAVING (((Portafoglio.TrattativaID)=442244486458333));"
    'Return the SQL text
    MyRecordSource = qry
End Function

That creates a temporary query for the Recordsource as long as report running.
But, keep in mind that the burden of the queries in a db is very small, thus, very small is the gain of your attempt as well.

Cheers,
John

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

thanks very much, don't ask why i'm doing something complicated for a little gain, i'm italian we are known for that

however, i was avoiding to use query def, and the real reason i want to have everything in vba was that once some queries went corrupted.

maybe it's impossible to run the query just in vba unfortunely..

**accesstos** · 03-02-2021, 05:16 PM

Originally Posted by diegomarino

thanks very much, don't ask why i'm doing something complicated for a little gain, i'm italian we are known for that

Greeks are better on this (una faccia una razza).

Originally Posted by diegomarino

maybe it's impossible to run the query just in vba unfortunely..

Did you test my suggestion? Maybe the issue is due to something else.

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

Originally Posted by accesstos

Greeks are better on this (una faccia una razza).

Did you test my suggestion? Maybe the issue is due to something else.

i just made a simple query def using the sql code (and yes, the FROM was just "porfatoglio"), and it works fine. When i close the report i delete the query
thanks

**Micron** · 03-02-2021, 05:52 PM

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)

error 2176 - too long

Thread Tools

error 2176 - too long

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