Tweaking a calculated field in a query

  • Thread starter Thread starter menken.john
  • Start date Start date
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.
 
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.
Click to expand...

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
 
Awesome, thanks very much.
Click to expand...

John's a pretty "awesome" guy when it comes to Access, databases, computer
applications, and a whole host of other topics.
 
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.
Click to expand...
Ensure that none of the both fields contains a NULL value and use the
"&" operator instead the "+".

Ulrich
 
Back
Top