Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access - need to return zero value in a query

Posted on 2013-01-14
2
Medium Priority
?
406 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

604 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