Subtracting null value or convert null to zero?

[latestgood]

Hello,

I have two tables. Table one has budget items (Groceries, Entertainment, Shopping) with budget for each category. Second table consists of expense receipts that's related to each category on table 1. I am trying to subtract table 1 by table 2 so that it'll be give net balance for that month. However, if I do not have any expenses for "Groceries" category, access does not pick it up. I want access to pick up the value like groceries. I tried using left join query, but it's not working.

Thank you,

 

[bob fitz]

Can you post the SQL of your query?

 

[boblarson]

To subtract, you need to handle the nulls with the NZ function. Or else a null-something or something-null or null+something or something+null will result in a null.

So in the query you can use

Nz([FieldNameHere],0) - Nz([FieldName2Here],0) + Nz([FieldName3Here],0)

etc.

 

[iliyanuhu]

In other word a number-unknown= unknown and vise versa, 2 - ? = ?

 

[boblarson]

In other word a number-unknown= unknown and vise versa, 2 - ? = ?

Good explanation.