?
Solved

MS Access - need to return zero value in a query

Posted on 2013-01-14
2
Medium Priority
?
405 Views
Last Modified: 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.
0
Comment
Question by:kolson14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1040 total points
ID: 38776551
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
 
LVL 1

Author Comment

by:kolson14
ID: 38776567
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial will teach you how to the overview of Microsoft Security Essentials. This is a free anti-virus software that guards your PC against viruses, spyware, worms, and other malicious software. This will be demonstrated using Windows…
This Micro Tutorial will give you basic overview of the control panel section on Windows 7. It will depth in Network and Internet, Hardware and Sound, etc. This will be demonstrated using Windows 7 operating system.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question