I have tried using the Nz function to return the value of '0' but still get null everytime and can't seem to get the syntax right. I also tried to use a IIf statement asking if null then show awaiting count, else show account count - depart count. What Im trying to over all is show the total of awaiting and as someone departed it will deduct from the awaiting total. I started biulduing the query in the form to solve for the counts but after realizing that if the depart value is null then the awaiting value would then be null too. I need the awaiting value to show its true value and then as they depart the awaiting value would be subtracted. Here is a couple of SQL I have tried so far.
SELECT Nz(([Awaiting Count].[CountOfBN]-[Departed Count].[CountOfBN]),0) AS Awaiting
FROM [Departed Count], [Awaiting Count];
SELECT IIf([Departed Count].[CountOfBN] Is Null,[Awaiting Count].[CountOfBN],[Awaiting Count].[CountOfBN]-[Departed Count].[CountOfBN]) AS Awaiting
FROM [Departed Count], [Awaiting Count];
I have tried other syntax but can't seem to nail it. I tried using the Nz function before and never had any success. Any help would be greatly appreciated.