Using the following code for a bill of materials search, I get results returned where the ItemCode = 1, AND the Bill of Materials numbers match. This only produces the list of products that the chosen customers have purchased.
What I really need it to do, is the same thing, but also list the other products that have an item code that =1 that were not purchased by the customer.
I've done what I know how to do with the JOIN query, but just cant seem to get the full item list without getting a bunch of errors. Any ideas?
where tblPartsList.ItemCode = 1
and tblBOMdetails.BomID = [frmBOMmainSheet].[BOMID]
SELECT tblPartsList.strPartNum, tblPartsList.StrStockNum, tblPartsList.strDescription, tblPartsList.Manufacturer, tblBOMdetails.QuantityRequested, tblInvCount.OnHand, tblBOMdetails.QuantityShipped, tblInvCount.OnOrder, tblInvCount.Remarks
FROM (tblPartsList LEFT JOIN tblBOMdetails ON tblPartsList.ID = tblBOMdetails.ItemID) LEFT JOIN tblInvCount ON tblBOMdetails.ItemID = tblInvCount.ItemID
WHERE (tblPartsList.ItemCode="1") AND (tblBOMdetails.BomID="1" Or tblBOMdetails.BomID Is Null);
Received the following error message with that code:
This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
It does appear to be typed correctly as far as all locations and tables go.
I think I used strings as the number fields . Take the "" out of the two "1" expressions.
If the above doesn't work, then you need to do two things to your original query:
1. tblPartsList needs to be left inner joined to tblBOMdetails (a join with an arrow). That will ensure all the parts are included.
In design view, double click on the join between these two tables and select the "Include all records from tblPartsList and only those records from tblBOMdetails where the join fields are equal."
2. Include null joins.
Because you have asked for records where BomID=1, then that's all you will get. But you want to include records where the right hand side of the join is null. So in design view, change the BomID criteria to:
1 or Is Null
You were correct...it was the "" causing the problems....and it works perfect now! Thank you tons for the help on this, as it should make work quite a bit easier... now that I can pull up a historical record on the computer, instead of searching through paperwork!
One last question though. I notice that when you enter code into this forum, it appears in it's own code box. How do you do that?