MS Access - need to return zero value in a query

I am having a difficult time with something I thought would be quite easy:

I have two tables in a query.  Credit Card Type and Receivables

I have created a new field in the Query called FEE which is calculation of values from both tables:
          Fee: [Credit Card Type]![Fee]*[Receivables]![Billing Revenue]+[Credit Card Type]![TransFee]

Then I have another field I created for the Query called ALLREVENUE, which gives a final total:
          AllRevenue: ([Billing Revenue]+[Other Income]-[Fee])

This works fine as long as there is a value in each field.  However, some transactions do not have a fee and as a result, ALLREVENUE is blank for that entry.  

All the other fields have been set at the table to have a default value of zero –However, the FEE field I created in this query does not have the option for a default value in the properties - so I guess I need to alter the above FEE calculation so if there is no fee associated with the billing revenue – the value is zero – not blank.

Hope that made sense and Thanks in advance.
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
use the NZ() function

AllRevenue: Nz([Billing Revenue])+ Nz([Other Income]) - Nz([Fee])

Fee: Nz([Credit Card Type]![Fee])* Nz([Receivables]![Billing Revenue]) + NZ([Credit Card Type]![TransFee])
kolson14Author Commented:
I was putting the Nz at the front of the statment rather than in front of each individual item...thanks so much!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.