What is wrong with this Query?

[SymbionA]

Hi,

I am running this query below in VBA. It is a long query I know and not pleasing to look at, however, I get the following run time error - 2342 "A runSQL action requires an argument consisting of an sql statement."

Could anybody help with what is wrong with the query?

Thanks in advance

strSQL = strSQL & " SELECT Mid([dbo_skul].[sp_code],1,1) AS Plnr, dbo_skulextra.char3 AS SupCde, dbo_skul.loc_no,"
strSQL = strSQL & " dbo_supplier.parent_loc, dbo_skul.item_no, Mid([dbo_skul2].[description],1,35) as descript,"
strSQL = strSQL & " dbo_skul2.std_cost, dbo_MRP_BOM_temp.adj_fcst0, dbo_MRP_BOM_temp.adj_fcst1,"
strSQL = strSQL & " dbo_MRP_BOM_temp.adj_fcst2, Int(([dbo_MRP_BOM_temp].[adj_fcst0] + [dbo_MRP_BOM_temp].[adj_fcst1] + [dbo_MRP_BOM_temp].[adj_fcst2]) / 3) AS AMF,"
strSQL = strSQL & " CInt(Right(Left([dbo_skul2].[description],47),5)) AS MTD, dbo_skul2.on_hand,"
strSQL = strSQL & " IIf(Mid([dbo_skul2].[description],67,5)=' ',0,Int(Mid([dbo_skul2].[description],67,5)))"
strSQL = strSQL & " AS [On Order], dbo_skul2.unshipped AS CBO,"
strSQL = strSQL & " [dbo_skul2].[on_hand]+Int(Mid([dbo_skul2].[description],67,5))-[dbo_skul2].[unshipped]"
strSQL = strSQL & " AS [Asset Stock], dbo_skul2.min_orq, InputFile.[Cost Change%], InputFile.[Future Cost Date],"
strSQL = strSQL & " IIf((([AMF]*2)<=[Asset Stock]),'',CInt((([AMF]*2)-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq])"
strSQL = strSQL & " AS Extra4Wks, IIf((([AMF]*2.5)<=[Asset Stock]),'',CInt((([AMF]*2.5)-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq])"
strSQL = strSQL & " AS Extra6Wks, IIf((([AMF]*3)<=[Asset Stock]),'',CInt((([AMF]*3)-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq])"
strSQL = strSQL & " AS Extra8Wks, IIf((([AMF]*4)<=[Asset Stock]),'',CInt((([AMF]*4)-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq])"
strSQL = strSQL & " AS Extra12Wks, IIf((([AMF]*(([NoofWeeks]/4)+1))<=[Asset Stock]),'',CInt((([AMF]*(([NoofWeeks]/4)+1))-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq]) AS [Managers Qty]"
strSQL = strSQL & " FROM IB_ManuCode INNER JOIN (InputFile2 INNER JOIN (dbo_MRP_BOM_temp INNER JOIN (dbo_skulextra"
strSQL = strSQL & " INNER JOIN (dbo_supplier INNER JOIN (dbo_skul2 INNER JOIN dbo_skul"
strSQL = strSQL & " ON dbo_skul2.skul_no = dbo_skul.skul_no) ON dbo_supplier.skul_no = dbo_skul.skul_no)"
strSQL = strSQL & " ON dbo_skulextra.skul_no = dbo_skul.skul_no) ON dbo_MRP_BOM_temp.skul_no = dbo_skul.skul_no)"
strSQL = strSQL & " ON InputFile2.[Item Code] = dbo_skul.item_no) ON IB_ManuCode.[Man Code] = dbo_skulextra.char3"
strSQL = strSQL & " WHERE (((dbo_skul.loc_no) <> 'BUN') And ((InputFile.[Cost Change%]) > 0.014999)"
strSQL = strSQL & " And ((InputFile.[Future Cost Date]) > Now()) And ((dbo_skulextra.char1) <> 'D'"
strSQL = strSQL & " And (dbo_skulextra.char1) <> 'R' And (dbo_skulextra.char1) <> 'A3') And ((dbo_skul2.dist_level) = 10))"
strSQL = strSQL & " ORDER BY dbo_skulextra.char3, dbo_skul.loc_no, Mid([dbo_skul2].[description],1,35);"

DoCmd.RunSQL strSQL

 

[PHV]

RunSQL is suited for action queries (UPDATE, DELETE, CREATE ...), not for SELECT.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SymbionA]

Thanks!

RunSQL should work for this query but it does not, however, I have added "Into OutputFile" to make a table.

It must be something else...

 

[dhookom]

What is your final query with the "into..."? Try use
debug.Print strSQL
to see what the actual final statement looks like. When pasted into the SQL view of a query, is it a make-table query?

As PH stated, RunSQL requires an action query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SymbionA]

OK the query with "INTO OUPUTFILE" is below:

How should I construct the query then?

strSQL = strSQL & "SELECT Mid([dbo_skul].[sp_code],1,1) AS Plnr, dbo_skulextra.char3 AS SupCde, dbo_skul.loc_no,"
strSQL = strSQL & " dbo_supplier.parent_loc, dbo_skul.item_no, Mid([dbo_skul2].[description],1,35) as descript,"
strSQL = strSQL & " dbo_skul2.std_cost, dbo_MRP_BOM_temp.adj_fcst0, dbo_MRP_BOM_temp.adj_fcst1,"
strSQL = strSQL & " dbo_MRP_BOM_temp.adj_fcst2, Int(([dbo_MRP_BOM_temp].[adj_fcst0] + [dbo_MRP_BOM_temp].[adj_fcst1] + [dbo_MRP_BOM_temp].[adj_fcst2]) / 3) AS AMF,"
strSQL = strSQL & " CInt(Right(Left([dbo_skul2].[description],47),5)) AS MTD, dbo_skul2.on_hand,"
strSQL = strSQL & " IIf(Mid([dbo_skul2].[description],67,5)=' ',0,Int(Mid([dbo_skul2].[description],67,5)))"
strSQL = strSQL & " AS [On Order], dbo_skul2.unshipped AS CBO,"
strSQL = strSQL & " [dbo_skul2].[on_hand]+Int(Mid([dbo_skul2].[description],67,5))-[dbo_skul2].[unshipped]"
strSQL = strSQL & " AS [Asset Stock], dbo_skul2.min_orq, InputFile2.[Cost Change%], InputFile2.[Future Cost Date],"
strSQL = strSQL & " IIf((([AMF]*2)<=[Asset Stock]),'',CInt((([AMF]*2)-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq])"
strSQL = strSQL & " AS Extra4Wks, IIf((([AMF]*2.5)<=[Asset Stock]),'',CInt((([AMF]*2.5)-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq])"
strSQL = strSQL & " AS Extra6Wks, IIf((([AMF]*3)<=[Asset Stock]),'',CInt((([AMF]*3)-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq])"
strSQL = strSQL & " AS Extra8Wks, IIf((([AMF]*4)<=[Asset Stock]),'',CInt((([AMF]*4)-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq])"
strSQL = strSQL & " AS Extra12Wks, IIf((([AMF]*(([NoofWeeks]/4)+1))<=[Asset Stock]),'',CInt((([AMF]*(([NoofWeeks]/4)+1))-[Asset Stock])/[dbo_skul2].[min_orq])*[dbo_skul2].[min_orq]) AS [Managers Qty] INTO OutputFile"
strSQL = strSQL & " FROM IB_ManuCode INNER JOIN (InputFile2 INNER JOIN (dbo_MRP_BOM_temp INNER JOIN (dbo_skulextra"
strSQL = strSQL & " INNER JOIN (dbo_supplier INNER JOIN (dbo_skul2 INNER JOIN dbo_skul"
strSQL = strSQL & " ON dbo_skul2.skul_no = dbo_skul.skul_no) ON dbo_supplier.skul_no = dbo_skul.skul_no)"
strSQL = strSQL & " ON dbo_skulextra.skul_no = dbo_skul.skul_no) ON dbo_MRP_BOM_temp.skul_no = dbo_skul.skul_no)"
strSQL = strSQL & " ON InputFile2.[Item Code] = dbo_skul.item_no) ON IB_ManuCode.[Man Code] = dbo_skulextra.char3"
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') And (dbo_skul2.dist_level) = 10)"
strSQL = strSQL & " ORDER BY dbo_skulextra.char3, dbo_skul.loc_no, Mid([dbo_skul2].[description],1,35);

 

[dhookom]

And..."Try use
debug.Print strSQL
to see what the actual final statement looks like. When pasted into the SQL view of a query, is it a make-table query?"

What do you get?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SymbionA]

Thank you all for your help, I have got it working.

I cannot tell you what the error was.... that is too embarassing! lol