Tweaking a calculated field in a query

[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.

 

[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

 

[menken.john]

Awesome, thanks very much.

 

[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.

 

[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