Access 2007 null values in query appear as #Deleted#
Posted on 2011-03-06
In an Access 2007 application that links SQL Server 2000 tables through ODBC I have created the following query:
SELECT OrderItems.ItemQuantity, OrderItems.ItemID, ItemComponents.ComponentQty, ItemComponents.ComponentID
FROM ((Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID) INNER JOIN BatchControl ON (Orders.ShipDate = BatchControl.ProcDate) AND (Orders.DateBatchID = BatchControl.ProcBatchID)) LEFT JOIN ItemComponents ON OrderItems.ItemID = ItemComponents.ItemID;
Some OrderItems do not have components, and one would expect that in rows where that is the case that the values for ComponentID and ComponentQty would be null. However, they show as #Deleted#. The foreign key ItemComponents.ItemID is indexed, but not unique.
As a check I created the following simple query to see just Items and their components, and it displayed null values as expected.
SELECT ItemMaster.ItemID, ItemComponents.ComponentQty, ItemComponents.ComponentID
FROM ItemMaster LEFT JOIN ItemComponents ON ItemMaster.ItemID = ItemComponents.ItemID;
It should be mentioned that I'm using Windows 7. I don't remember ever having this type of problem with XP or Vista, but I also don't know that I ever attempted to do this exact sort of thing. It seems like I must have at one time or another, but I can't be sure.
Any help would be appreciated.