MS Access - need to return zero value in a query
Posted on 2013-01-14
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.