I'm not even sure that you can structure a select statement this way but I'm trying to subtract the items that have exited from the items that have been damaged to figure my current inventory.
There may or may not be a value in SumExits for each ItemID. I have checked the Control variable and it is giving me the appropriate ItemID but rs!Inventory continues to be null.
strSQL = "SELECT UnitsDamaged - nz(SELECT SumExits FROM qrySumExits WHERE ItemID = " & Control & ",0)AS Inventory FROM [qryUnitsDamaged] WHERE ItemID = " & Control
Set rs = CurrentDb.OpenRecordset(strSQL)
Forms!frmExit!txtInInventory.Value = rs!Inventory
There may or may not be a value in SumExits for each ItemID. I have checked the Control variable and it is giving me the appropriate ItemID but rs!Inventory continues to be null.
strSQL = "SELECT UnitsDamaged - nz(SELECT SumExits FROM qrySumExits WHERE ItemID = " & Control & ",0)AS Inventory FROM [qryUnitsDamaged] WHERE ItemID = " & Control
Set rs = CurrentDb.OpenRecordset(strSQL)
Forms!frmExit!txtInInventory.Value = rs!Inventory