Allen Brownes Inventory code

[bbrazeau]

I used Allen Brownes code to make an Inventory example where the "On Hand" is calculated. I then wanted to tried and make the Invoice side of it work for Products instead of just parts. (Products are assemblies of parts)
So I wrote a query to parse the part qty per product and the product qty per Invoice then multiply them together in an expression "LIPP1" (see code) I then went to SQL mode and copied the SQL statement. Then went to the 'Get the quantity used since then.' section of his code and replaced his strSQL with mine. I then tried to used his syntax for Sum and criteria to atempt to get the total parts used. I get runtime error 3075 with "missing operator". Could anyone tell me if they see the problem or even if I'm going about this the right way? I know if I can Sum the LIPP1 parameter of my query it works out mathimatically. I just can't get it to work in code.

strSQL = "SELECT Sum (tblInvoice.InvoiceID, tblInvoiceDetail.ProductID, tblInvoiceDetail.Quantity " & _
" AS tblInvoiceDetail_Quantity, tblProductDetail.PartID, tblProductDetail.Quantity AS tblProductDetail_Quantity, " & _
" [tblInvoiceDetail_Quantity]*[tblProductDetail_Quantity] AS LIPP1) As QuantityUsed " & _
" FROM (tblProduct INNER JOIN tblProductDetail ON tblProduct.[ProductID] = tblProductDetail.[ProductID]) INNER JOIN " & _
" (tblInvoice INNER JOIN tblInvoiceDetail ON tblInvoice.[InvoiceID] = tblInvoiceDetail.[InvoiceID]) ON tblProduct.[ProductID] = tblInvoiceDetail.[ProductID] " & _
" WHERE ((tblProductDetail.PartID)= " & lngPart & ")"

Set rs = db.OpenRecordset(strSQL) Hangs on this line, but problem is in the SQL statement above.

[ssanfu]

You don't (shouldn't ) sum the tblInvoice.InvoiceID, tblInvoiceDetail.ProductID or tblProductDetail.PartID fields.


Try adding a Debug.Print statement strSQL statement befort the "Set rs = ... " line
Set a breakpoint on the "Set rs = ... " line
Copy the text from the immediate window and paste it into a new query (SQL view) to test it.

[June7]

Certainly should not include all the fields in one Sum expression. Sum and other aggregate functions are used in GROUP BY (Totals) queries. Use the query builder to help construct the sql statement.

Or consider a report that uses Grouping & Sorting features with aggregate calcs in footer sections.

What do you want to do with the recordset object?

[orange]

Further to comments from June and Steve, I suggest you look at some SQL examples at http://www.w3schools.com/sql/sql_select.asp

Good material on Sum and Group etc.

[bbrazeau]

Thanks for all the help on this. In the end the solution I came to was to simply base my recordset on a query instead of trying to extract it from the tables. The query "OrdProdPart" has a calculated field "TotalPartsUsed" which gives the total parts used by each product in every order times the number of products ordered. When you Sum this total where Part = Part selected in a combobox, it gets the total parts used in all orders by every Product that uses that part. When you subtract this from all parts acquired or all parts acquired after the optional StockTakeDate it returns parts "OnHand". The basis of my error was using a method I seen sugested before for building an SQL statment that said: write a query to gather the information you need then switch to SQL mode and copy the SQL statment. Then paste it in your code and it should be close barring some syntax adjustments. In this case it did not work.

strSQL = "SELECT Sum (TotalPartsUsed)As QuantityUsed " & _
" FROM OrdProdPart WHERE ((OrdProdPart.Part= " & lngPart & ")"