Working Query doesn't work attached to Form!

  • Thread starter Thread starter ksteger
  • Start date Start date
K

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
 
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])
....
Click to expand...
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.
Click to expand...

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
 
Back
Top