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.
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]!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER