Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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.
0
kolson14
Asked:
kolson14
1 Solution
 
Rey Obrero (Capricorn1)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])
0
 
kolson14Author Commented:
I was putting the Nz at the front of the statment rather than in front of each individual item...thanks so much!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now