ERROR 2176 : sql length too long

[franksirvent]

Hi
I have an SQL 'sentence' which seems to be to long to run (2176 characters).

Is run from a form's VB and it gives me error 2176.

I've read in the forum that the maximum length is 2048

I've tried to shorten the sequence as much as possible (using FROM [My table] as g everywhere, etc) however the field names cannot be changed as this will means that I will have to change all the tables and will affect the whole application.
I don't see how I can shorten it anymore than already is.

HOWEVER I have copied the SQL sequence to Access Queries and IT RUNS FINE. It doesn't advise that the length is too long like it does from with VB.

1. Anyone knows why the difference (in runs in Access queries but not from VB) ?
2. Is it possible to 'transfer' the sequence to Access Queries, run it, then pass the result back to the form where the original SQL sequence is been run from ?
3. If question 2 is possible, how do I do it ?

Thanks in advance

 

[ZmrAbdulla]

You can split SQL into parts..
BTW, By any chance you could post your SQL? Seen SQL like anything here that will take "years" to read once.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[ZmrAbdulla]

also to add the note:
Maximum length of an SQL statement in VBA is 32,768 characters that is capable of taking SQL 15 times longer than your SQL

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[franksirvent]

Hi there,

Thanks for replying

I have read about the 2048 character limit in this forum (search for 2176 under 'Queries')

I have done as you told me, and broken down the SQL into two smaller 'blocks', then add them together like this:

sqlline=sqlline1 & lf & sqlline2
Me.Recordsource=sqlline

This is the code as requested.
As mentioned, when run directly in QBE it works fine.


SELECT g.ShippingID, g.ValidDate, g.ToDate,g.Type, mami(g.POL) as POL, mami(g.POD) as POD,
g.service,g.frequency,g.[20freightcur],g.[40freightcur],g.fac,g.incentive,g.[20freight],g.[40freight],g.[40hcfreight],
get_BAF(g.[20baf],12/11/2005, g.ToDate,g.direction) as g20_BAF,
get_BAF(g.[40baf],12/11/2005 , g.ToDate,g.direction) as g40_BAF,
get_CAF(g.[Freight1],12/11/2005 , g.ToDate,g.direction) as g20_CAF,
(nz(g.[20Freight],0)+nz(g20_BAF,0)+nz(g.[freight1],0)+nz(g.[freight2],0)+nz(g.[freight3],0)+nz(g.[freight4],0)) AS A_2,
(nz(g.[40Freight],0)+nz(g40_BAF,0)+nz(g.[freight6],0)+nz(g.[freight7],0)+nz(g.[freight8],0)+nz(g.[freight9],0)) AS A_4,
(nz(g.[40HCFreight],0)+nz(g40_BAF,0)+nz(g.[freight6],0)+nz(g.[freight7],0)+nz(g.[freight8],0)+nz(g.[freight9],0)) AS A_H,
g.[Commodity],g.[Id],g.[GRI],"Global Buying Rates" AS TabletoLook FROM ([Global Buying Rates] as g) WHERE (((g.Type)="F")
AND ((g.validdate)<=#12/11/2005#) AND ((g.todate)>=#12/11/2005# Or (g.todate) Is Null)
AND ((g.POL) like '*Valencia*') AND ((g.shippingid) like '**') AND ((g.POD) like '*Shanghai*'))

UNION ALL SELECT l.ShippingID, l.ValidDate, l.ToDate,l.Type, mami(l.POL) as POL, mami(l.POD) as POD,

l.service,l.frequency,l.[20freightcur],l.[40freightcur],l.fac,l.incentive,l.[20freight],l.[40freight],l.[40hcfreight],
get_BAF(l.[20baf],12/11/2005, l.ToDate,l.direction) as l20_BAF,
get_BAF(l.[40baf],12/11/2005, l.ToDate,l.direction) as l40_BAF,
get_CAF(l.[Freight1],12/11/2005 , l.ToDate,l.direction) as g20_CAF,
(nz(l.[20Freight],0)+nz(l20_BAF,0)+nz(l.[freight1],0)+nz(l.[freight2],0)+nz(l.[freight3],0)+nz(l.[freight4],0)) AS A_2, (nz(l.[40Freight],0)+nz(l40_BAF,0)+nz(l.[freight6],0)+nz(l.[freight7],0)+nz(l.[freight8],0)+nz(l.[freight9],0)) AS A_4, (nz(l.[40HCFreight],0)+nz(l40_BAF,0)+nz(l.[freight6],0)+nz(l.[freight7],0)+nz(l.[freight8],0)+nz(l.[freight9],0)) AS A_H,
l.[Commodity],l.[Id],l.[GRI],"Local Buying Rates" AS TabletoLook FROM ([Local Buying Rates] as l) WHERE (((l.Type)="F")
AND ((l.ToDate)>=#12/11/2005# Or (l.ToDate) Is Null) AND ((l.POL) like '*Valencia*') AND ((l.shippingid) like '**')
AND ((l.POD) like '*Shanghai*')) ORDER BY A_2;

 

[ZmrAbdulla]

I have tried to format the SQL. Below the result

sSQL = "SELECT         g.shippingid, g.validdate, g.todate,g.type, mami(g.pol) as pol, mami(g.pod) as pod, g.service,g.frequency,g.[20freightcur],g.[40freightcur],g.fac,g.incentive,g.[20freight],g.[40freight],g.[40hcfreight], get_baf(g.[20baf],12/11/2005, g.todate,g.direction) as g20_baf, get_baf(g.[40baf],12/11/2005, g.todate,g.direction) as g40_baf, get_caf(g.[freight1],12/11/2005, g.todate,g.direction) as g20_caf, (nz(g.[20freight],0)+nz(g20_baf,0)+nz(g.[freight1],0)+nz(g.[freight2],0)+nz(g.[freight3],0)+nz(g.[freight4],0)) as a_2, (nz(g.[40freight],0)+nz(g40_baf,0)+nz(g.[freight6],0)+nz(g.[freight7],0)+nz(g.[freight8],0)+nz(g.[freight9],0)) as a_4, (nz(g.[40hcfreight],0)+nz(g40_baf,0)+nz(g.[freight6],0)+nz(g.[freight7],0)+nz(g.[freight8],0)+nz(g.[freight9],0)) as a_h, g.[commodity],g.[id],g.[gri],"global buying rates" as tabletolook " & _ 
       "FROM           ([global buying rates] as g) " & _ 
       "WHERE         (((g.type)="f")" & _ 
       " AND          ((g.validdate)<=#12/11/2005#)" & _ 
       " AND          ((g.todate)>=#12/11/2005# or (g.todate) is null)" & _ 
       " AND          ((g.pol) like '*valencia*')" & _ 
       " AND          ((g.shippingid) like '**')" & _ 
       " AND          ((g.pod) like '*shanghai*')) " & _ 
       "UNION ALL     " & _ 
       " SELECT         l.shippingid, l.validdate, l.todate,l.type, mami(l.pol) as pol, mami(l.pod) as pod, l.service,l.frequency,l.[20freightcur],l.[40freightcur],l.fac,l.incentive,l.[20freight],l.[40freight],l.[40hcfreight], get_baf(l.[20baf],12/11/2005, l.todate,l.direction) as l20_baf, get_baf(l.[40baf],12/11/2005, l.todate,l.direction) as l40_baf, get_caf(l.[freight1],12/11/2005, l.todate,l.direction) as g20_caf, (nz(l.[20freight],0)+nz(l20_baf,0)+nz(l.[freight1],0)+nz(l.[freight2],0)+nz(l.[freight3],0)+nz(l.[freight4],0)) as a_2, (nz(l.[40freight],0)+nz(l40_baf,0)+nz(l.[freight6],0)+nz(l.[freight7],0)+nz(l.[freight8],0)+nz(l.[freight9],0)) as a_4, (nz(l.[40hcfreight],0)+nz(l40_baf,0)+nz(l.[freight6],0)+nz(l.[freight7],0)+nz(l.[freight8],0)+nz(l.[freight9],0)) as a_h, l.[commodity],l.[id],l.[gri],"local buying rates" as tabletolook " & _ 
       "FROM           ([local buying rates] as l) " & _ 
       "WHERE         (((l.type)="f")" & _ 
       " AND          ((l.todate)>=#12/11/2005# or (l.todate) is null)" & _ 
       " AND          ((l.pol) like '*valencia*')" & _ 
       " AND          ((l.shippingid) like '**')" & _ 
       " AND          ((l.pod) like '*shanghai*')) " & _ 
       "ORDER BY       a_2;"

that is 2540 chars..
and the other one

sSQL = "SELECT         g.shippingid" & _ 
       ",              g.validdate" & _ 
       ",              g.todate" & _ 
       ",             g.type" & _ 
       ",              mami(g.pol) as pol" & _ 
       ",              mami(g.pod) as pod" & _ 
       ",              g.service" & _ 
       ",             g.frequency" & _ 
       ",             g.[20freightcur]" & _ 
       ",             g.[40freightcur]" & _ 
       ",             g.fac" & _ 
       ",             g.incentive" & _ 
       ",             g.[20freight]" & _ 
       ",             g.[40freight]" & _ 
       ",             g.[40hcfreight]" & _ 
       ",              get_baf(g.[20baf]" & _ 
       ",             12/11/2005" & _ 
       ",              g.todate" & _ 
       ",             g.direction) as g20_baf" & _ 
       ",              get_baf(g.[40baf]" & _ 
       ",             12/11/2005" & _ 
       ",              g.todate" & _ 
       ",             g.direction) as g40_baf" & _ 
       ",              get_caf(g.[freight1]" & _ 
       ",             12/11/2005" & _ 
       ",              g.todate" & _ 
       ",             g.direction) as g20_caf" & _ 
       ",              (nz(g.[20freight]" & _ 
       ",             0)+nz(g20_baf" & _ 
       ",             0)+nz(g.[freight1]" & _ 
       ",             0)+nz(g.[freight2]" & _ 
       ",             0)+nz(g.[freight3]" & _ 
       ",             0)+nz(g.[freight4]" & _ 
       ",             0)) as a_2" & _ 
       ",              (nz(g.[40freight]" & _ 
       ",             0)+nz(g40_baf" & _ 
       ",             0)+nz(g.[freight6]" & _ 
       ",             0)+nz(g.[freight7]" & _ 
       ",             0)+nz(g.[freight8]" & _ 
       ",             0)+nz(g.[freight9]" & _ 
       ",             0)) as a_4" & _ 
       ",              (nz(g.[40hcfreight]" & _ 
       ",             0)+nz(g40_baf" & _ 
       ",             0)+nz(g.[freight6]" & _ 
       ",             0)+nz(g.[freight7]" & _ 
       ",             0)+nz(g.[freight8]" & _ 
       ",             0)+nz(g.[freight9]" & _ 
       ",             0)) as a_h" & _ 
       ",              g.[commodity]" & _ 
       ",             g.[id]" & _ 
       ",             g.[gri]" & _ 
       ",             "global buying rates" as tabletolook " & _ 
       "FROM           ([global buying rates] as g) " & _ 
       "WHERE         (((g.type)="f")" & _ 
       " AND          ((g.validdate)<=#12/11/2005#)" & _ 
       " AND          ((g.todate)>=#12/11/2005# or (g.todate) is null)" & _ 
       " AND          ((g.pol) like '*valencia*')" & _ 
       " AND          ((g.shippingid) like '**')" & _ 
       " AND          ((g.pod) like '*shanghai*')) " & _ 
       "UNION ALL     " & _ 
       " SELECT         l.shippingid" & _ 
       ",              l.validdate" & _ 
       ",              l.todate" & _ 
       ",             l.type" & _ 
       ",              mami(l.pol) as pol" & _ 
       ",              mami(l.pod) as pod" & _ 
       ",              l.service" & _ 
       ",             l.frequency" & _ 
       ",             l.[20freightcur]" & _ 
       ",             l.[40freightcur]" & _ 
       ",             l.fac" & _ 
       ",             l.incentive" & _ 
       ",             l.[20freight]" & _ 
       ",             l.[40freight]" & _ 
       ",             l.[40hcfreight]" & _ 
       ",              get_baf(l.[20baf]" & _ 
       ",             12/11/2005" & _ 
       ",              l.todate" & _ 
       ",             l.direction) as l20_baf" & _ 
       ",              get_baf(l.[40baf]" & _ 
       ",             12/11/2005" & _ 
       ",              l.todate" & _ 
       ",             l.direction) as l40_baf" & _ 
       ",              get_caf(l.[freight1]" & _ 
       ",             12/11/2005" & _ 
       ",              l.todate" & _ 
       ",             l.direction) as g20_caf" & _ 
       ",              (nz(l.[20freight]" & _ 
       ",             0)+nz(l20_baf" & _ 
       ",             0)+nz(l.[freight1]" & _ 
       ",             0)+nz(l.[freight2]" & _ 
       ",             0)+nz(l.[freight3]" & _ 
       ",             0)+nz(l.[freight4]" & _ 
       ",             0)) as a_2" & _ 
       ",              (nz(l.[40freight]" & _ 
       ",             0)+nz(l40_baf" & _ 
       ",             0)+nz(l.[freight6]" & _ 
       ",             0)+nz(l.[freight7]" & _ 
       ",             0)+nz(l.[freight8]" & _ 
       ",             0)+nz(l.[freight9]" & _ 
       ",             0)) as a_4" & _ 
       ",              (nz(l.[40hcfreight]" & _ 
       ",             0)+nz(l40_baf" & _ 
       ",             0)+nz(l.[freight6]" & _ 
       ",             0)+nz(l.[freight7]" & _ 
       ",             0)+nz(l.[freight8]" & _ 
       ",             0)+nz(l.[freight9]" & _ 
       ",             0)) as a_h" & _ 
       ",              l.[commodity]" & _ 
       ",             l.[id]" & _ 
       ",             l.[gri]" & _ 
       ",             "local buying rates" as tabletolook " & _ 
       "FROM           ([local buying rates] as l) " & _ 
       "WHERE         (((l.type)="f")" & _ 
       " AND          ((l.todate)>=#12/11/2005# or (l.todate) is null)" & _ 
       " AND          ((l.pol) like '*valencia*')" & _ 
       " AND          ((l.shippingid) like '**')" & _ 
       " AND          ((l.pod) like '*shanghai*')) " & _ 
       "ORDER BY       a_2;"

with 5948 chars.
Try if any of them are working.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[franksirvent]

wow! ZmrAbdulla, that's a lot of work you've done. I appreciate you taking the time to do help me.

I've tried the first one only...has exactly the same problem as mine: "Error 2176, string is too long"

But if I paste your code to QBE and run it directly from there, it runs fine...

It seems like there is a problem running this: me.recordsource = sSQL

that´s where the problem is occuring.

I've in fact done the following:

me.recordsource = "query from QBE" (where 'query from QBE' is your Query you've sent me, which I've saved as a query) and it runs FINE too.

So the problem must be related with me.recordsource = sSQL, not with the SQL itself.

I've also tried to break down the query in two and add it as follows:

Me.RecordSource = sqlline1 & sqlline2

This doesn't work either !!!!!!

It is driving me NUTS !!!!!!

Thanks anyway...

 

[ZmrAbdulla]

As the error message says not the sql but some procedure inside the sql is creating the problem.

The setting for this property is too long.@You can enter up to either 255 or 2,048 characters for this property, depending on the data type.@@1@@1

Did you try separating the nested nz function into a part and join it's result to the sql?

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[franksirvent]

wow!

That stuff about 'separating nz function...' is a bt over my knowledge I think.

I am not sure what you mean.

Could you give me an example?

I was thinking of adding new fields to the table where data comes from, so I don't need to use the....

(nz(l.[20freight]" & _
", 0)+nz(l20_baf" & _
", 0)+nz(l.[freight1]" & _
", 0)+nz(l.[freight2]" & _
", 0)+nz(l.[freight3]" & _
", 0)+nz(l.[freight4]" & _
", 0)) as a_2"

It should save a lot of characters as there are many of them...It will require me to add 3 new fields to the table but perhaps that's the solultion as I am going nuts !

Also, how do make the code to go into a table on the forum? I've looked around but I've not been able to read how to do it.
It makes sense as it is easier to read.

Thanks

 

[ZmrAbdulla]

I am not that expert on SQL .. still just a sample.

    Dim strNZa, strNZb, sSQL, TotalSQL As String
    sSQL = "SELECT Issue.ItemName , Issue.Rate,Issue.Quantity, Issue.Amount,"
    strNZa = "Nz([Rate],0)+Nz([Quantity],0)+Nz([Amount],0)As nzvalue1,"
    strNZb = "Nz([Rate],0)+Nz([Quantity],0)+Nz([Amount],0)As nzvalue2 FROM Issue;"
    TotalSQL = sSQL & strNZa & strNZb
    Me.List2.RowSource = TotalSQL

Try joining the parts one by one with the TotalSQL. Remember to add , and FROM TableName where ever necessary.
Then you can easily find out the where exactly it creates problem.
Also noticed missing square brackets for the field names in the NZ fields.

(nz(l.[20freight]" & _ 
       ",             0)+nz([b]l20_baf[/b]" & _ 
       ",             0)+nz(l.[freight1]" & _ 
       ",             0)+nz(l.[freight2]" & _ 
       ",             0)+nz(l.[freight3]" & _ 
       ",             0)+nz(l.[freight4]" & _ 
       ",             0)) as a_2" & _

and remove a star from Like '**' though it is not going to be a problem.


________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[mp9]

It's just an observation and it's also a long shot... but you don't need square brackets around all your field names. As long as your field names don't have spaces/special characters in (which they don't) and aren't reserved words (which they're not) you can do without them.

By my reckoning if you remove all the [ and ] from your SQL it just about squeezes under the 2048 character limit.

That's not to say there might not be other problems with the SQL, but I just thought I'd mention this...

 

[franksirvent]

thanks ZmrAbdulla,

I will try your idea, that might work. I'll let you know.

thanks mp9, that's a good idea too, that will take some characters off!

Thank you both for your help !

 

[PHV]

I guess the limiation applies to the RecordSource property.
You may try to create a query with this SQL code and set the RecordSource property to the name of this query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[franksirvent]

Yes PHV, that was my first idea, as I knew it worked, as mentioned on the thread.

After much thinking I thought of creating the SQL with VB then saving it as a Query, then make:
me.recordsource="my new query" where 'my new query' is the one saved from VB.

As the SQL changes constantly (depending on the fields selected on the form) and I already have the SQL written on code, I would like to save the already created SQL into a Query (so it shows in the Query Window of Access) which I can then use...

Do you know how to do that ?
(I don't even know if it s possible)