Length of a String

[SymbionA]

I am creating a String in VBA for a SQLquery.

Does anybody know the maximum length of characters I can use to create a string?

I do not want to call the query iteself since I am parsing parameter values into the query.

Thanks in advance.

 

[Zion7]

Access 2000.

Sort limit.................255 characters in or more fields
N° of AND's & WHERE's in Having Clause........40
N° of characters in SQL statement.............64,000


...that's all I know, in regardes to your question.

 

[SymbionA]

Thanks for your reply.

I am not asking you to decode this, however, I get a run time error 3067 Query Input must contain one table or query. I am wondering if this is because strSQL is too long and it is being truncated?

However, it is not 64,000 characters either, would you know why I am getting an error?

Thanks!
------
strSQL = strSQL & "SELECT Mid([dbo_skul]![sp_code],1,1) AS Plnr, [dbo_skulextra].[char3], [dbo_skul].[loc_no],"
strSQL = strSQL & "[dbo_supplier].[parent_loc] , [dbo_skul].[item_no], Mid([dbo_skul2]![Description], 1, 35)"
strSQL = strSQL & "AS Descript, [dbo_skul2].[std_cost], [dbo_history].[y2m8], [dbo_history].[y2m9],"
strSQL = strSQL & "[dbo_history].[y2m10], [dbo_history].[y2m11], [dbo_history].[y2m12],"
strSQL = strSQL & "Int(([dbo_history]![y2m12]+[dbo_history]![y2m11]+[dbo_history]![y2m10]+"
strSQL = strSQL & "[dbo_history]![y2m9]+[dbo_history]![y2m8]+[dbo_history]![y2m7]+[dbo_history]![y2m6]"
strSQL = strSQL & "+[dbo_history]![y2m5])/8) AS [Avg Mth], CInt(Right(Left([dbo_skul2]![description],47),5))"
strSQL = strSQL & "AS MTD, [dbo_skul2].[on_hand], IIf(Mid([dbo_skul2]![description],67,5)=' ',0,"
strSQL = strSQL & "Int(Mid([dbo_skul2]![description],67,5))) AS [On Order], [dbo_skul2].[unshipped],"
strSQL = strSQL & "[dbo_skul2]![on_hand]+([dbo_rep_gd1]![pd_ero]+[dbo_rep_gd1]![ero1]+[dbo_rep_gd1]![ero2]"
strSQL = strSQL & "+[dbo_rep_gd1]![ero3]+[dbo_rep_gd1]![ero4]+[dbo_rep_gd1]![ero5]+[dbo_rep_gd1]![ero6]+"
strSQL = strSQL & "[dbo_rep_gd1]![ero7]+[dbo_rep_gd1]![ero8]+[dbo_rep_gd1]![ero9]+[dbo_rep_gd1]![ero10]+"
strSQL = strSQL & "[dbo_rep_gd1]![ero11]+[dbo_rep_gd1]![ero12]+[dbo_rep_gd1]![ero13]+[dbo_rep_gd1]![ero14]"
strSQL = strSQL & "+[dbo_rep_gd1]![ero15]+[dbo_rep_gd1]![ero16]+[dbo_rep_gd1]![ero17]+[dbo_rep_gd1]![ero18]+"
strSQL = strSQL & "[dbo_rep_gd1]![ero19]+[dbo_rep_gd1]![ero20]+[dbo_rep_gd1]![ero21]+[dbo_rep_gd1]![ero22]+"
strSQL = strSQL & "[dbo_rep_gd1]![ero23]+[dbo_rep_gd1]![ero24]+[dbo_rep_gd1]![ero25]+[dbo_rep_gd1]![ero26])-"
strSQL = strSQL & "[dbo_skul2]![unshipped] AS [Asset Stock], [dbo_skul2].[min_orq], [InputFile2].[Cost Change%],"
strSQL = strSQL & "[InputFile2].[Future Cost Date], IIf((([Avg Mth]*2)>[Asset Stock]),CInt((([Avg Mth]*2)-"
strSQL = strSQL & "[Asset Stock])/[dbo_skul2]![min_orq]),'') AS [Minorqs to buy4], IIf(([Minorqs to buy4]=''),'',"
strSQL = strSQL & "IIf([Minorqs to buy4]=0,'',[Minorqs to buy4]*[dbo_skul2]![min_orq])) AS [Extra 4 Wks],"
strSQL = strSQL & "IIf((([Avg Mth]*2.5)>[Asset Stock]),CInt((([Avg Mth]*2.5)-[Asset Stock])/[dbo_skul2]![min_orq]),'')"
strSQL = strSQL & "AS [Minorqs to buy6], IIf(([Minorqs to buy6]=''),'',IIf([Minorqs to buy6]=0,'',"
strSQL = strSQL & "[Minorqs to buy6]*[dbo_skul2]![min_orq])) AS [Extra 6 Wks], IIf((([Avg Mth]*3)>[Asset Stock]),"
strSQL = strSQL & "CInt((([Avg Mth]*3)-[Asset Stock])/[dbo_skul2]![min_orq]),'') AS"
strSQL = strSQL & "[Minorqs to buy8], IIf(([Minorqs to buy8]=''),'',IIf([Minorqs to buy8]=0,'',"
strSQL = strSQL & "[Minorqs to buy8]*[dbo_skul2]![min_orq])) AS [Extra 8 Wks], IIf((([Avg Mth]*4)>"
strSQL = strSQL & "[Asset Stock]),CInt((([Avg Mth]*4)-[Asset Stock])/[dbo_skul2]![min_orq]),'') AS"
strSQL = strSQL & "[Minorqs to buy12], IIf(([Minorqs to buy12]=''),'',IIf([Minorqs to buy12]=0,'',"
strSQL = strSQL & "[Minorqs to buy12]*[dbo_skul2]![min_orq])) AS [Extra 12 Wks] INTO OutputFile"
strSQL = strSQL & "FROM IB_ManuCode INNER JOIN (InputFile2 INNER JOIN (dbo_skulextra INNER JOIN"
strSQL = strSQL & "(dbo_supplier INNER JOIN (dbo_rep_gd1 INNER JOIN (dbo_skul2 INNER JOIN"
strSQL = strSQL & "(dbo_history INNER JOIN dbo_skul ON [dbo_history].[skul_no]=[dbo_skul].[skul_no])"
strSQL = strSQL & "ON [dbo_skul2].[skul_no]=[dbo_skul].[skul_no]) ON [dbo_rep_gd1].[skul_no]=[dbo_skul]."
strSQL = strSQL & "[skul_no]) ON [dbo_supplier].[skul_no]=[dbo_skul].[skul_no]) ON [dbo_skulextra].[skul_no]="
strSQL = strSQL & "[dbo_skul].[skul_no]) ON [InputFile2].[Item Code]=[dbo_skul].[item_no]) ON [IB_ManuCode]."
strSQL = strSQL & "[Man Code] = [InputFile2].[Man Code]"
strSQL = strSQL & "WHERE ((([dbo_skul].[loc_no]) <> 'BUN') And (([InputFile2].[Cost Change%]) > 0.014999)"
strSQL = strSQL & "And (([InputFile2].[Future Cost Date]) > Now()) And (([dbo_skulextra].[char1]) <> 'd'"
strSQL = strSQL & "And ([dbo_skulextra].[char1]) <> 'r' And ([dbo_skulextra].[char1]) <> 'A3')"
strSQL = strSQL & "And (([dbo_skul2].[dist_level]) = 10))"
strSQL = strSQL & "ORDER BY [dbo_skulextra].[char3], [dbo_skul].[loc_no], [dbo_skul].[item_no];

 

[Zion7]

I Don't believe the bang operator "!", is an invalid SQL character? ...Replace with "."
Secondly, your spacing (or lack of), is syntatically incorrect.

you need to asdd space to beginning of every line...eg

strSQL = strSQL & " [dbo_skul].[skul_no]) ON [InputFile2].[Item Code]=[dbo_skul].[item_no]) ON [IB_ManuCode]."
strSQL = strSQL & " [Man Code] = [InputFile2].[Man Code]"
strSQL = strSQL & " WHERE ((([dbo_skul].[loc_no]) <> 'BUN') And (([InputFile2].[Cost Change%]) > 0.014999)"


add a space to beginning of every line strSQL = strSQL & " [dbo...

 

[SymbionA]

It is a large SQL statement that I inherited, however, it now works.

Thank you for your advice.

It is appreciated.

 

[cmmrfrds]

What is the definition of strSQL?

Dim strSQL ??????

 

[Zion7]

cmmrfrds,
Variant, is the the default data type in VBA.
It's very legal to declare a variable without specifying the
data type, as above.
The result, always a variant (very taxing, if not necessary).

SymbionA, was "my' advice correct, or was it something else?

 

[SymbionA]

OK, the definition of strSQL is String.

Zion7,

Yeah thank you for your advice it was correct, however, the two lines below did not need a space due to the fullstop at the very end.

After changing the bang operator to "." and adding the spaces it worked fine.

Thanks again!

strSQL = strSQL & "ON [dbo_skul2].[skul_no]=[dbo_skul].[skul_no]) ON [dbo_rep_gd1].[skul_no]=[dbo_skul]."
strSQL = strSQL & "[dbo_skul].[skul_no]) ON [InputFile2].[Item Code]=[dbo_skul].[item_no]) ON [IB_ManuCode]."