Using the Nz function (Access 07)

[jonny3000]

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.

[June7]

Why isn't there a JOIN clause in that query to join the two tables on common unique ID?

The Nz() expression has unnecessary pair of parens but should still work with tables properly joined.

[jonny3000]

I can't join the two querys. I am trying to count the amount of awaiting and departed and did so in a seperate query. I know I can do within the form but just trying different methods to try and get the Nz function to work.

[June7]

If you don't define a join on common ID fields, then every record in each table will join to every record in the other table.

I don't understand what you are trying to do.

[DatabaseMakers]

what about:

SELECT (Nz([Awaiting Count].[CountOfBN],0)-Nz([Departed Count].[CountOfBN],0)) AS Awaiting
FROM [Departed Count], [Awaiting Count];

[dreday]

If you don't define a join on common ID fields, then every record in each table will join to every record in the other table.

I don't understand what you are trying to do.

This is correct, what you have is won't generate anything useful from a cross join.