SQL QUERY TOO LONG ?

[fsweb2002]

HI...

Is there a limit on to how long an SQL query can be???
I have the following query which gives a TOO LONG error.

I´ve used & vbCrLf which I found in one of the threads, however it doesn´t seem to help...

ANy ideas...


lafecha = Format(Date, "mm/dd/yyyy&quot

sqlline = sqlline & "SELECT [Global Buying Rates].ShippingID, [Global Buying Rates].ValidDate, [Global Buying Rates].ToDate,"
sqlline = sqlline & "[Global Buying Rates].GRI,[Global Buying Rates].Service, [Global Buying Rates].Frequency,[Global Buying Rates].Commodity,"
sqlline = sqlline & "[Global Buying Rates].Type, [Global Buying Rates].POL, [Global Buying Rates].POD, [Global Buying Rates].[20Freight], "
sqlline = sqlline & "[Global Buying Rates].[20FreightCUR], [Global Buying Rates].[40Freight],[Global Buying Rates].[40FreightCUR], [Global Buying Rates].[FAC],"
sqlline = sqlline & "[Global Buying Rates].[Incentive], [Global Buying Rates].[Id],[20Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt20FreightVIP,"
sqlline = sqlline & "" & """" & "Global Buying Rates" & """" & " AS TabletoLook",[40Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt40FreightVIP
sqlline = sqlline & " FROM [Global Buying Rates]"
sqlline = sqlline & " WHERE ((([Global Buying Rates].ShippingID)" & txtShippingLine & &quot"
sqlline = sqlline & " AND (([Global Buying Rates].Type)" & txttype & &quot"
sqlline = sqlline & " AND (([Global Buying Rates].ToDate)=#" & lafecha & "# Or ([Global Buying Rates].ToDate)>#" & lafecha & "# Or ([Global Buying Rates].ToDate) Is Null) "
sqlline = sqlline & " AND (([Global Buying Rates].POL)" & txtPOL & &quot "
sqlline = sqlline & " AND (([Global Buying Rates].POD)" & txtPOD & &quot) " & vbCrLf

sqlline = sqlline & " UNION ALL "


sqlline = sqlline & "SELECT [Local Buying Rates].ShippingID, [Local Buying Rates].ValidDate, [Local Buying Rates].ToDate,"
sqlline = sqlline & "[Local Buying Rates].GRI,[Local Buying Rates].Service, [Local Buying Rates].Frequency,[Local Buying Rates].Commodity,"
sqlline = sqlline & "[Local Buying Rates].Type, [Local Buying Rates].POL, [Local Buying Rates].POD, [Local Buying Rates].[20Freight], "
sqlline = sqlline & "[Local Buying Rates].[20FreightCUR], [Local Buying Rates].[40Freight],[Local Buying Rates].[40FreightCUR], [Local Buying Rates].[FAC],"
sqlline = sqlline & "[Local Buying Rates].[Incentive], [Local Buying Rates].[Id],[20Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt20FreightVIP,"
sqlline = sqlline & " " & """" & "Global Buying Rates" & """" & " AS TabletoLook",[40Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt40FreightVIP,
sqlline = sqlline & " FROM [Local Buying Rates]"
sqlline = sqlline & " WHERE ((([Local Buying Rates].ShippingID)" & txtShippingLine & &quot"
sqlline = sqlline & " AND (([Local Buying Rates].Type)" & txttype & &quot"
sqlline = sqlline & " AND (([Local Buying Rates].ToDate)=#" & lafecha & "# Or ([Local Buying Rates].ToDate)>#" & lafecha & "# Or ([Local Buying Rates].ToDate) Is Null) "
sqlline = sqlline & " AND (([Local Buying Rates].POL)" & txtPOL & &quot "
sqlline = sqlline & " AND (([Local Buying Rates].POD)" & txtPOD & &quot)" & CheaptoExpensive & ";"
mas:
Me.RecordSource = sqlline

 

[jjonesal]

I think the real limitation is the size of a string (255 chars).

What about adding table aliases in your sql statement (for example use l as a table alias for Local Buying Rates, and g as an alias for Global Buying Rates)

That would make your string lots smaller and it might work...
J. Jones
jjones@cybrtyme.com

 

[jjonesal]

Something like:

sqlline = sqlline & "SELECT g.ShippingID, g.ValidDate, g.ToDate,"
sqlline = sqlline & "g.GRI,g.Service, g.Frequency,g.Commodity,"
sqlline = sqlline & "g.Type, g.POL, g.POD, g.[20Freight], "
sqlline = sqlline & "g.[20FreightCUR], g.[40Freight],g.[40FreightCUR], g.[FAC],"
sqlline = sqlline & "g.[Incentive], g.[Id],[20Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt20FreightVIP,"
sqlline = sqlline & "" & """" & "Global Buying Rates" & """" & " AS TabletoLook",[40Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt40FreightVIP
sqlline = sqlline & " FROM [Global Buying Rates] AS g"
sqlline = sqlline & " WHERE (((g.ShippingID)" & txtShippingLine & &quot"
sqlline = sqlline & " AND ((g.Type)" & txttype & &quot"
sqlline = sqlline & " AND ((g.ToDate)=#" & lafecha & "# Or (g.ToDate)>#" & lafecha & "# Or (g.ToDate) Is Null) "
sqlline = sqlline & " AND ((g.POL)" & txtPOL & &quot "
sqlline = sqlline & " AND ((g.POD)" & txtPOD & &quot) " & vbCrLf

sqlline = sqlline & " UNION ALL "


sqlline = sqlline & "SELECT l.ShippingID, l.ValidDate, l.ToDate,"
sqlline = sqlline & "l.GRI,l.Service, l.Frequency,l.Commodity,"
sqlline = sqlline & "l.Type, l.POL, l.POD, l.[20Freight], "
sqlline = sqlline & "l.[20FreightCUR], l.[40Freight],l.[40FreightCUR], l.[FAC],"
sqlline = sqlline & "l.[Incentive], l.[Id],[20Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt20FreightVIP,"
sqlline = sqlline & " " & """" & "Global Buying Rates" & """" & " AS TabletoLook",[40Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt40FreightVIP,
sqlline = sqlline & " FROM [Local Buying Rates] AS l"
sqlline = sqlline & " WHERE (((l.ShippingID)" & txtShippingLine & &quot"
sqlline = sqlline & " AND ((l.Type)" & txttype & &quot"
sqlline = sqlline & " AND ((l.ToDate)=#" & lafecha & "# Or (l.ToDate)>#" & lafecha & "# Or (l.ToDate) Is Null) "
sqlline = sqlline & " AND ((l.POL)" & txtPOL & &quot "
sqlline = sqlline & " AND ((l.POD)" & txtPOD & &quot)" & CheaptoExpensive & ";"
mas:
Me.RecordSource = sqlline
J. Jones
jjones@cybrtyme.com

 

[jjonesal]

Also - I'd take out the vbCrlf's J. Jones
jjones@cybrtyme.com

 

[cmmrfrds]

You could break the sql statement into a few strings and combine at the recordsource.

Me.RecordSource = sql1 & sql2 & sql3

 

[Steve101]

An extention to jjonesal's posts regarding using alias's, this is usually a good idea.

Since your Select statements are single tables, you dont even need the alias prefix in this instance.

Also, you might try using the line continuation instead of concatenation.

For example:

sqlline = "SELECT ShippingID, ValidDate, ToDate," & _
"GRI,Service, Frequency,Commodity," & _
"Type, POL, POD, [20Freight]," & _
"[20FreightCUR], [40Freight],[40FreightCUR], [FAC]," & _
"[Incentive], [Id],[20Freight]*((100-nz([FAC]))/100)-nz([Incentive]) AS txt20FreightVIP," & _
....
....

and so on. This also makes the code a little more legible. You can also save the odd character here and there by removing spaces after commas between fields, unnecessary square brackets and possibly redundant round brackets.

Note that if you are cutting the query out of the Query design window, into a code procedure, that Access does not produce optimally presented SQL; hense it will give you redundant brackets, table prefixes on fields etc. Hense its quite legitimate to remove these where you recognise them.

I presume that the two tables being unioned are of different structure, and/or you're only interested in a subset of the fields; otherwise you could use a 'SELECT *' approach.

All else failing, try setting up two query definitions to prepare the data in the form you want it (ie. to include the SELECT fields you want and based on the the filter criteria you want); then all you'll need above is something like:

SELECT *
FROM qryYourFirstQry
UNION
SELECT *
FROM qryYourSecondQry

(Which could also be done via a third querydef).

With this approach, you could pass any variables through to the queries as references to confrols on a form. Lots of options available.

Hope some of this helps,

Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[fsweb2002]

thank you all for such helpful replies.
I will try them out later today.

 

[fsweb2002]

jjonesal

IT WORKS GREAT !!!!!!

THANKS TO ALL FOR YOUR FANTASTIC INPUT !!!!