Long subquery

[diegomarino]

you're right, fixed it and Sql string works fine as recordsource

but if i use sql as subquery like that

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

i get the same error.

[diegomarino]

i tried this one

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

and it worked! maybe the problem is the union query?

[hci_jon]

Your subquery you are trying to build is too long.
A recordsource of a control can only contain 2048 characters, so when you are trying to insert the subquery twice this is causing your string to go beyond the 2048 character limit, so that is what the error is saying.
As you found, putting the sql in a saved query object fixes this, since you only have to reference the name of the saved query object rather than have all the sql in there.

If you want to use your SQL instead, you will have to modify it to be shorter. You can do this by aliasing tables. Consider this query:

SELECT Myreallylongtablename.MyReallyLongFieldName FROM Myreallylongtablename

And notice how it is shortened:

SELECT m.MyReallyLongFieldName FROM Myreallylongtablename m

In your query this should shorten it quite a bit.

If that doesn't make it short enough, you could make a query based on your original table object and also alias the field names to make them shorter, however you are back to needing another saved query.

[diegomarino]

thanks very much, very useful suggestion