Invalid argument to function 3072 when trying to add any table to existing query

[davesaintjay]

I am trying to add a table to expand this query but keep getting this error. It doesn't seem to matter what table I try to add I still get this error.

Can anyone help?

This code works but the next one does not. The only difference is the new table I've added.

SELECT Customers.schoolname, Orders.invoiceid, products.ProductName, products.UnitPrice, [Order Details].discount, [UnitPrice]*[Quantity]-[discount] AS soldfor, Orders.orderdate, [Order Details].Quantity, Orders.postage, Orders.postagepaid, [postagepaid]/[itemsinpackage] AS postagepaidper, mailshots.mailshotname, cdr.cdrunit, cdlabels.cdlabelunit, cdlabelprint.cdlabelprintunit, [cdrunit]+[cdlabelunit]+[cdlabelprintunit] AS cdtotaloverhead, dvdr.dvdrunit, dvdprint.dvdprintunit, dvdcover.dvdcoverunit, dvdcoverprint.dvdcoverprintunit, dvdcases.dvdcaseunit, [dvdrunit]+[dvdprintunit]+[dvdcoverunit]+[dvdcoverprintunit]+[dvdcaseunit] AS dvdtotaloverhead, products.Pages, supplyorders.supplypercost, blackprint.blackprintper, [blackprintper]+[supplypercost] AS blacktotalunit, [blacktotalunit]*[Pages] AS blackpages, products.[Cover Pages], colourprint.colourprintunit, [supplypercost]+[colourprintunit] AS colourtotalunit, [colourtotalunit]*[Cover Pages] AS colourpages, [blackpages]+[colourpages] AS inhouseprint, wallets.walletunit, [blackprintper]/16 AS barcodeprint, [addresslabelper]+[barcodeprint] AS barcodeunit, sellotape.sellotapeunit, cello.cellounit, [cdtotaloverhead]+[walletunit]+[barcodeunit]+[cellounit]+[sellotapeunit] AS inhousenollprint, Purchase.purchaseunit, [dvdtotaloverhead]+[inhouseprint]+[inhousenollprint]+[purchaseunit] AS productoverhead, [productoverhead]*[Quantity] AS productoverheads, mailshots.salesprint, [salesprint]/10000 AS salesprintunit, Orders.invoiceused, [invoiceoverheadcost]/10000000000000000 AS invoicetotalcost, Orders.envelopeused, envelopes.envelopeunit, Orders.postagelabelused, [postageprintcost]/[postageprintquantity]/[postagelabelperpage] AS postagelabelprintunit, [postagelabelunit]+[postagelabelprintlinkedunit] AS postagelabeltotal, postagelabels.postagelabelunit, postagelabelprintlinked.postagelabelprintlinkedunit, Orders.returnlabelused, [returnlabelunit]+[returnprintboughtunit] AS returnlabeltotal, returnlabels.returnlabelunit, returnlabelprintlinked.returnprintboughtunit, [salesprintunitper]+[invoicetotalcost]+[packingper] AS orderoverheads, mailshots.sellerrate, Round([sellerrate]/100*[soldfor],2) AS sellercut, mailshots.taxrate, Round([sellercut]*[taxrate]/100,2) AS sellercuttax, Orders.ordersource, [Order Details].itemno, mailshots.fixed, mailshots.variable, Round([fixed]*[taxrate]/100,2) AS fixedtax, [fixed]+[fixedtax] AS fixedtotal, Round([variable]*[taxrate]/100,2) AS variabletax, [variable]+[variabletax] AS variabletotal, Round([fixedtotal]+[variabletotal]+[sellertotal]+[paymentmethodstotal],10) AS totalfees, [sellercut]+[sellercuttax]+[ebaypostagefee] AS sellertotal, [soldfor]+[postage]-[totalfees] AS trans, Round([postagerate]*[postage]/100,2) AS ebaypostagefee, paymentmethods.paymentmethodsrate, [paymentmethodsrate]*([soldfor]+[postage])/1000 AS paymentmethodscut, paymentmethods.paymentmethodscharge, [paymentmethodscharge]/[multiple] AS paymentmethodschargeper, Round([paymentmethodscut]+[paymentmethodschargeper]+[crossborderchargeamount],2) AS paymentmethodstotal, paymentmethods.paymentmethodsname, Customers.crossbordercountry, crossborderpayments.crossborderrate, paymentmethods.crossbordercharge, [crossbordercharge]*([soldfor]+[postage])*[crossborderrate]/1000 AS crossborderchargeamount, Customers.address3, mailshots.postagerate, Round([trans]-[productoverheads]-[orderoverheads]-[postagepaidper],10) AS profit, Orders.multiple, Orders.itemsinpackage, [envelopeunit]+[postagelabeltotal]+[returnlabeltotal] AS packing, [packing]/[itemsinpackage] AS packingper, [salesprintunit]/[multiple] AS salesprintunitper
FROM mailshots INNER JOIN ((crossborderpayments INNER JOIN Customers ON crossborderpayments.ID = Customers.crossbordercountry) INNER JOIN (invoiceoverheads INNER JOIN (cdlabelprint INNER JOIN (dvdcoverprint INNER JOIN (dvdcover INNER JOIN (dvdr INNER JOIN (cdr INNER JOIN (Purchase INNER JOIN (cdlabels INNER JOIN (sellotape INNER JOIN (wallets INNER JOIN (addresslabels INNER JOIN (cello INNER JOIN (blackprint INNER JOIN (colourprint INNER JOIN (supplyorders INNER JOIN (colourprint2 INNER JOIN (dvdprint INNER JOIN ((dvdcases INNER JOIN ProductType ON dvdcases.ID = ProductType.dvdcase) INNER JOIN (postagelabelprintlinked INNER JOIN (returnlabelprintlinked INNER JOIN (returnlabels INNER JOIN (paymentmethods INNER JOIN (postagelabels INNER JOIN (envelopes INNER JOIN ((Orders INNER JOIN [Order Details] ON Orders.invoiceid = [Order Details].OrderID) INNER JOIN products ON [Order Details].productid = products.ProductID) ON envelopes.ID = Orders.envelopeused) ON postagelabels.ID = Orders.postagelabelused) ON paymentmethods.paymentmethodsID = Orders.paidvia) ON returnlabels.ID = Orders.returnlabelused) ON returnlabelprintlinked.ID = returnlabels.returnlabelprintlinked) ON postagelabelprintlinked.ID = postagelabels.postagelabelprintused) ON ProductType.ID = products.producttype) ON dvdprint.ID = ProductType.dvdprint) ON colourprint2.ID = ProductType.colourink2) ON supplyorders.ID = ProductType.paperused) ON colourprint.ID = ProductType.colourink) ON blackprint.ID = ProductType.blackink) ON cello.ID = ProductType.celloused) ON addresslabels.ID = ProductType.barcodelabel) ON wallets.ID = ProductType.walletused) ON sellotape.ID = ProductType.sellotape) ON cdlabels.ID = ProductType.cdlabel) ON Purchase.purchaseno = products.purchaseused) ON cdr.ID = ProductType.cdr) ON dvdr.ID = ProductType.dvdr) ON dvdcover.ID = ProductType.dvdcover) ON dvdcoverprint.ID = ProductType.dvdcoverprint) ON cdlabelprint.ID = ProductType.cdlabelprint) ON invoiceoverheads.ID = Orders.invoiceused) ON Customers.customerid = Orders.customerid) ON mailshots.ID = Orders.ordersource
WHERE (((Orders.orderdate) Between #1/1/2017# And #1/31/2017#))
ORDER BY Orders.invoiceid DESC;

THis one doesn't work:-

SELECT Customers.schoolname, Orders.invoiceid, products.ProductName, products.UnitPrice, [Order Details].discount, [UnitPrice]*[Quantity]-[discount] AS soldfor, Orders.orderdate, [Order Details].Quantity, Orders.postage, Orders.postagepaid, [postagepaid]/[itemsinpackage] AS postagepaidper, mailshots.mailshotname, cdr.cdrunit, cdlabels.cdlabelunit, cdlabelprint.cdlabelprintunit, [cdrunit]+[cdlabelunit]+[cdlabelprintunit] AS cdtotaloverhead, dvdr.dvdrunit, dvdprint.dvdprintunit, dvdcover.dvdcoverunit, dvdcoverprint.dvdcoverprintunit, dvdcases.dvdcaseunit, [dvdrunit]+[dvdprintunit]+[dvdcoverunit]+[dvdcoverprintunit]+[dvdcaseunit] AS dvdtotaloverhead, products.Pages, supplyorders.supplypercost, blackprint.blackprintper, [blackprintper]+[supplypercost] AS blacktotalunit, [blacktotalunit]*[Pages] AS blackpages, products.[Cover Pages], colourprint.colourprintunit, [supplypercost]+[colourprintunit] AS colourtotalunit, [colourtotalunit]*[Cover Pages] AS colourpages, [blackpages]+[colourpages] AS inhouseprint, wallets.walletunit, [blackprintper]/16 AS barcodeprint, [addresslabelper]+[barcodeprint] AS barcodeunit, sellotape.sellotapeunit, cello.cellounit, [cdtotaloverhead]+[walletunit]+[barcodeunit]+[cellounit]+[sellotapeunit] AS inhousenollprint, Purchase.purchaseunit, [dvdtotaloverhead]+[inhouseprint]+[inhousenollprint]+[purchaseunit] AS productoverhead, [productoverhead]*[Quantity] AS productoverheads, mailshots.salesprint, [salesprint]/10000 AS salesprintunit, Orders.invoiceused, [invoiceoverheadcost]/10000000000000000 AS invoicetotalcost, Orders.envelopeused, envelopes.envelopeunit, Orders.postagelabelused, [postageprintcost]/[postageprintquantity]/[postagelabelperpage] AS postagelabelprintunit, [postagelabelunit]+[postagelabelprintlinkedunit] AS postagelabeltotal, postagelabels.postagelabelunit, postagelabelprintlinked.postagelabelprintlinkedunit, Orders.returnlabelused, [returnlabelunit]+[returnprintboughtunit] AS returnlabeltotal, returnlabels.returnlabelunit, returnlabelprintlinked.returnprintboughtunit, [salesprintunitper]+[invoicetotalcost]+[packingper] AS orderoverheads, mailshots.sellerrate, Round([sellerrate]/100*[soldfor],2) AS sellercut, mailshots.taxrate, Round([sellercut]*[taxrate]/100,2) AS sellercuttax, Orders.ordersource, [Order Details].itemno, mailshots.fixed, mailshots.variable, Round([fixed]*[taxrate]/100,2) AS fixedtax, [fixed]+[fixedtax] AS fixedtotal, Round([variable]*[taxrate]/100,2) AS variabletax, [variable]+[variabletax] AS variabletotal, Round([fixedtotal]+[variabletotal]+[sellertotal]+[paymentmethodstotal],10) AS totalfees, [sellercut]+[sellercuttax]+[ebaypostagefee] AS sellertotal, [soldfor]+[postage]-[totalfees] AS trans, Round([postagerate]*[postage]/100,2) AS ebaypostagefee, paymentmethods.paymentmethodsrate, [paymentmethodsrate]*([soldfor]+[postage])/1000 AS paymentmethodscut, paymentmethods.paymentmethodscharge, [paymentmethodscharge]/[multiple] AS paymentmethodschargeper, Round([paymentmethodscut]+[paymentmethodschargeper]+[crossborderchargeamount],2) AS paymentmethodstotal, paymentmethods.paymentmethodsname, Customers.crossbordercountry, crossborderpayments.crossborderrate, paymentmethods.crossbordercharge, [crossbordercharge]*([soldfor]+[postage])*[crossborderrate]/1000 AS crossborderchargeamount, Customers.address3, mailshots.postagerate, Round([trans]-[productoverheads]-[orderoverheads]-[postagepaidper],10) AS profit, Orders.multiple, Orders.itemsinpackage, [envelopeunit]+[postagelabeltotal]+[returnlabeltotal] AS packing, [packing]/[itemsinpackage] AS packingper, [salesprintunit]/[multiple] AS salesprintunitper
FROM mailshots INNER JOIN ((crossborderpayments INNER JOIN Customers ON crossborderpayments.ID = Customers.crossbordercountry) INNER JOIN (worldfirst INNER JOIN (invoiceoverheads INNER JOIN (cdlabelprint INNER JOIN (dvdcoverprint INNER JOIN (dvdcover INNER JOIN (dvdr INNER JOIN (cdr INNER JOIN (Purchase INNER JOIN (cdlabels INNER JOIN (sellotape INNER JOIN (wallets INNER JOIN (addresslabels INNER JOIN (cello INNER JOIN (blackprint INNER JOIN (colourprint INNER JOIN (supplyorders INNER JOIN (colourprint2 INNER JOIN (dvdprint INNER JOIN ((dvdcases INNER JOIN ProductType ON dvdcases.ID = ProductType.dvdcase) INNER JOIN (postagelabelprintlinked INNER JOIN (returnlabelprintlinked INNER JOIN (returnlabels INNER JOIN (paymentmethods INNER JOIN (postagelabels INNER JOIN (envelopes INNER JOIN ((Orders INNER JOIN [Order Details] ON Orders.invoiceid = [Order Details].OrderID) INNER JOIN products ON [Order Details].productid = products.ProductID) ON envelopes.ID = Orders.envelopeused) ON postagelabels.ID = Orders.postagelabelused) ON paymentmethods.paymentmethodsID = Orders.paidvia) ON returnlabels.ID = Orders.returnlabelused) ON returnlabelprintlinked.ID = returnlabels.returnlabelprintlinked) ON postagelabelprintlinked.ID = postagelabels.postagelabelprintused) ON ProductType.ID = products.producttype) ON dvdprint.ID = ProductType.dvdprint) ON colourprint2.ID = ProductType.colourink2) ON supplyorders.ID = ProductType.paperused) ON colourprint.ID = ProductType.colourink) ON blackprint.ID = ProductType.blackink) ON cello.ID = ProductType.celloused) ON addresslabels.ID = ProductType.barcodelabel) ON wallets.ID = ProductType.walletused) ON sellotape.ID = ProductType.sellotape) ON cdlabels.ID = ProductType.cdlabel) ON Purchase.purchaseno = products.purchaseused) ON cdr.ID = ProductType.cdr) ON dvdr.ID = ProductType.dvdr) ON dvdcover.ID = ProductType.dvdcover) ON dvdcoverprint.ID = ProductType.dvdcoverprint) ON cdlabelprint.ID = ProductType.cdlabelprint) ON invoiceoverheads.ID = Orders.invoiceused) ON worldfirst.ID = Orders.transferno) ON Customers.customerid = Orders.customerid) ON mailshots.ID = Orders.ordersource
WHERE (((Orders.orderdate) Between #1/1/2017# And #1/31/2017#))
ORDER BY Orders.invoiceid DESC;

[ranman256]

Did you put this in a query to see what error you get?
did you build this in a query?
argument errors usu are due to the functions. It could be Round.

[davesaintjay]

Did you put this in a query to see what error you get?
did you build this in a query?
argument errors usu are due to the functions. It could be Round.

THanks for the reply ;-)

Yes this is the code from the query and I get the Invalid argument to function which shows up on the help link as error 3072.

I tried removing all the round functions and I still get the same error.

[aytee111]

As you can see, the Keep It Simple philosophy was created for this type of scenario. Separate this out into multiple queries and it will be far easier to troubleshoot.

[ssanfu]

Maybe see https://msdn.microsoft.com/en-us/lib...ffice.12).aspx ???

(from https://msdn.microsoft.com/en-us/lib...ffice.12).aspx )