Tweaking a calculated field in a query


M

menken.john

Hi, I have a calculated field in my query called "Test" and here is the equation.
Test: [overhead]+[travel]
Unfortunately if the travel column is blank it gives me no result. It's like Access is saying, "Hey, unless I see numbers in both overhead AND travel I'm not giving you a sum number. Is there a way that I can get a result in the cell even if the travel column holds no value?
Thanks very much.
 
Ad

Advertisements

J

John W. Vinson

Hi, I have a calculated field in my query called "Test" and here is the equation.
Test: [overhead]+[travel]
Unfortunately if the travel column is blank it gives me no result. It's like Access is saying, "Hey, unless I see numbers in both overhead AND travel I'm not giving you a sum number. Is there a way that I can get a result in the cell even if the travel column holds no value?
Thanks very much.
Yes, you can use the builtin NZ() - Null To Zero - function:

Test: NZ([overhead]) + NZ([travel])

NZ also has an optional second argument if zero isn't what you want: e.g.

TaxAmount: [SaleAmt] * NZ([TaxRate], 1)

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
A

Access Developer

Awesome, thanks very much.
John's a pretty "awesome" guy when it comes to Access, databases, computer
applications, and a whole host of other topics.
 
Ad

Advertisements

U

Ulrich Möller

Am 04.12.2013 19:55, schrieb (e-mail address removed):
Hi, I have a calculated field in my query called "Test" and here is the equation.
Test: [overhead]+[travel]
Unfortunately if the travel column is blank it gives me no result. It's like Access is saying, "Hey, unless I see numbers in both overhead AND travel I'm not giving you a sum number. Is there a way that I can get a result in the cell even if the travel column holds no value?
Thanks very much.
Ensure that none of the both fields contains a NULL value and use the
"&" operator instead the "+".

Ulrich
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top