invalid argument error with query

[robojeff]

When my query is set to a select query it runs just fine but when I make it a make table query I get an invalid argument error. I getthe same error if I try to close the query design view window with either query configuration (make table or Select query):

Select Query SQL:

SELECT M.BPROD AS ASM, M.BCHLD AS Item, I.IDESC AS [Desc], M.BQREQ AS Quantity, I.IUMS AS Unit, M.BBUBB AS [Bubble#], M.BDDIS AS [Effective From], M.BDEFF AS [Effective Thru]
FROM JDE1noDup LEFT JOIN (M LEFT JOIN I ON M.BCHLD = I.IPROD) ON JDE1noDup.Item = M.BPROD
WHERE (((M.BPROD)=[Forms]![DataPull_frm]![Pnum]) AND ((M.BDDIS)=99999999 Or (M.BDDIS)>=CLng(Format(Now(),"yyyymmdd"))) AND ((M.BDEFF)<=CLng(Format(Now(),"yyyymmdd"))) AND ((M.BMWHS)="01") AND ((M.BMBOMM)=" ")) OR (((I.IDESC) Not Like "*ALPHA*")) OR (((I.IDESC) Not Like "*NON-RELEASE*")) OR (((I.IDESC) Not Like "ALPHA REV FOR DOC CONTROL "))
ORDER BY M.BPROD, M.BBUBB;
SF_MBM.BPROD, M.BBUBB;

Make table query:

SELECT M.BPROD AS ASM, M.BCHLD AS Item, I.IDESC AS [Desc], M.BQREQ AS Quantity, I.IUMS AS Unit, M.BBUBB AS [Bubble#], M.BDDIS AS [Effective From], M.BDEFF AS [Effective Thru] INTO JDE_BOM_tbl
FROM JDE1noDup LEFT JOIN (M LEFT JOIN I ON M.BCHLD = I.IPROD) ON JDE1noDup.Item = M.BPROD
WHERE (((M.BPROD)=[Forms]![DataPull_frm]![Pnum]) AND ((M.BDDIS)=99999999 Or (M.BDDIS)>=CLng(Format(Now(),"yyyymmdd"))) AND ((M.BDEFF)<=CLng(Format(Now(),"yyyymmdd"))) AND ((M.BMWHS)="01") AND ((M.BMBOMM)=" ")) OR (((I.IDESC) Not Like "*ALPHA*")) OR (((I.IDESC) Not Like "*NON-RELEASE*")) OR (((I.IDESC) Not Like "ALPHA REV FOR DOC CONTROL "))
ORDER BY M.BPROD, M.BBUBB;

Can't figure out what is causing the invalid argument error... Any suggestions?

 

[lespaul]

maybe try this instead:

INSERT INTO JDE_BOM_tbl (SELECT M.BPROD, M.BCHLD, I.IDESC, M.BQREQ, I.IUMS, M.BBUBB, M.BDDIS, M.BDEFF
FROM JDE1noDup LEFT JOIN (M LEFT JOIN I ON M.BCHLD = I.IPROD) ON JDE1noDup.Item = M.BPROD
WHERE (((M.BPROD)=[Forms]![DataPull_frm]![Pnum]) AND ((M.BDDIS)=99999999 Or (M.BDDIS)>=CLng(Format(Now(),"yyyymmdd"))) AND ((M.BDEFF)<=CLng(Format(Now(),"yyyymmdd"))) AND ((M.BMWHS)="01") AND ((M.BMBOMM)="  ")) OR (((I.IDESC) Not Like "*ALPHA*")) OR (((I.IDESC) Not Like "*NON-RELEASE*")) OR (((I.IDESC) Not Like "ALPHA REV FOR DOC CONTROL     ")))

Does JDE_BOM_tbl only have fields listed in your SELECT clause? If not you'll need to quantify which fields you are inserting into:

INSERT INTO JDE_BOM_tbl (FieldName1, FieldName2, etc) (SELECT .....)

you'll probably need to get rid of the ORDER BY clause in the INSERT query.

Leslie

Have you met Hardy Heron?