Working Query doesn't work attached to Form!

[ksteger]

The query I wrote involves 2 tables. The first table has a list of
item numbers being selected based on checkbox1 being "Yes". The second
table I want all data for records that match the item numbers from the
first table being selected based on a particular date. So in the end I
will have items with other data attached to it and items with no data
attached to it.
The following is the basic query:

SELECT a.[ItemNumber], b.[Date], b.[ItemNumber], b.[Category],
b.[Inventory_Begin], b.[Inventory_End], b.[Shipments],
b.[InedibleBeginInventory], b.[InedibleEndInventory],
b.[InedibleShipped], b.[Group], b.[Production], b.[E_WIP],
b.[B_WIP], b.[Lbs], b.[Cases], b.[Football], b.[CostCenter],
b.[Class], a.[Class], a.[CostCenter]
FROM Lookup_ItemNums a LEFT JOIN DailyYieldByItem b
ON a.[ID]=b.[ItemNumber]
WHERE (((b.Date)=[Forms]![F_DailyYieldByItem_Params]![RptDate])
AND ((a.DailyDeboneReport)=Yes))
OR (((a.DailyDeboneReport)=Yes)
AND ((a.ID) Not In (Select c.ID
From Lookup_ItemNums c,
DailyYieldByItem d
WHERE c.ID = d.ItemNumber
AND d.Date = b.Date)));

The sub-query in the where clause was put in because when I originally
added the date criteria the query then acted as though the join was an
"Inner Join". Now the trick is it works by itself in query builder.
The instant I attach it to the form it seems to no longer recognizes
the date parameter. Any thoughts would be greatly appreciated.

Thanks!
-Kim

 

[Emilia Maxim]

The query I wrote involves 2 tables. The first table has a list of
item numbers being selected based on checkbox1 being "Yes". The second
table I want all data for records that match the item numbers from the
first table being selected based on a particular date. So in the end I
will have items with other data attached to it and items with no data
attached to it.
The following is the basic query:

SELECT a.[ItemNumber], b.[Date], b.[ItemNumber], b.[Category],
b.[Inventory_Begin], b.[Inventory_End], b.[Shipments],
b.[InedibleBeginInventory], b.[InedibleEndInventory],
b.[InedibleShipped], b.[Group], b.[Production], b.[E_WIP],
b.[B_WIP], b.[Lbs], b.[Cases], b.[Football], b.[CostCenter],
b.[Class], a.[Class], a.[CostCenter]
FROM Lookup_ItemNums a LEFT JOIN DailyYieldByItem b
ON a.[ID]=b.[ItemNumber]
WHERE (((b.Date)=[Forms]![F_DailyYieldByItem_Params]![RptDate])
....

The sub-query in the where clause was put in because when I originally
added the date criteria the query then acted as though the join was an
"Inner Join". Now the trick is it works by itself in query builder.
The instant I attach it to the form it seems to no longer recognizes
the date parameter. Any thoughts would be greatly appreciated.

Kim,

you should rename the field called 'Date'. Date is a built in function
in Access, so using it to name user defined objects is asking for
really big trouble - which you obviously already got.

If 'Date' is much used in your DB, go to the link and download
Find&Replace:

http://www.ricksworld.com

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de