Link to home
Start Free TrialLog in
Avatar of kolson14
kolson14

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kolson14
kolson14

ASKER

I was putting the Nz at the front of the statment rather than in front of each individual item...thanks so much!