473,383 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

Trying to use the NZ Function with #num! as a result

5
I have a form with a calculated field (Business #1 DCR)see attachment. The field has a data Control Source of: =([N/IBus1]+[DepAmorBus1]+[IntExpBus1])/([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1])with a Default Value of 0. Which works fine if there are numbers in the related fields, but produces #Num! if the related fields all have the default 0. I would like result to be blank or have a 0 as a result if the related fields contain the default 0. I have tried numerous Nz calculations but have not been successful. Anyone have any ideas how to resolve this? Any help would be greatly appreciated. Thanks

Attached Images
File Type: jpg FormPic.jpg (81.3 KB, 501 views)
Jan 30 '12 #1

✓ answered by NeoPa

You describe a situation where you have Divide by Zero errors (#Num!). This is entirely unrelated to nulls in any form.

To avoid these errors you need to check for a result of zero (0) in your divisor, for such a formula is incalculable. Use IIf() to do this, as in :
Expand|Select|Wrap|Line Numbers
  1. =IIf([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]=0,0,([N/IBus1]+[DepAmorBus1]+[IntExpBus1])/([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]))

2 1788
Rabbit
12,516 Expert Mod 8TB
Nz is to be used with nulls. From the sounds of it, you don't have nulls. You're trying to divide by 0 and that's causing an error. Use the iif() function to display a different result if the denominators are 0.
Jan 30 '12 #2
NeoPa
32,556 Expert Mod 16PB
You describe a situation where you have Divide by Zero errors (#Num!). This is entirely unrelated to nulls in any form.

To avoid these errors you need to check for a result of zero (0) in your divisor, for such a formula is incalculable. Use IIf() to do this, as in :
Expand|Select|Wrap|Line Numbers
  1. =IIf([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]=0,0,([N/IBus1]+[DepAmorBus1]+[IntExpBus1])/([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]))
Jan 30 '12 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Chris Allen | last post by:
Hi, I'm (slowly) trying to get to grips with PHP and MySQL. I'm running: Red Hat 7.2 Apache 1.3.20 Mysql 3.32.41 PHP 4.1.2 A simple connect
9
by: frizzle | last post by:
Hi there, Short question: Is there any way, to reverse the result of a mysql query? Explanation: If i have eg. 20 records, all with their own id of course, select 5 with limit, and order...
17
by: rox.scott | last post by:
Can someone please explain why this happens? The expected output is 3, but uncommenting line 7 makes the output 0. Why ??? VB.NET code: ** note the commented line, this is the culprit ** Dim...
1
by: Hoang Duc Chau | last post by:
Hi Guy, Dim dTestA As Decimal = Decimal.Round(6.75, 1) dTestA return 6.8 (right) Dim dTestB As Decimal = Decimal.Round(6.65, 1) dTestB return 6.6 (false) while i expected the result is 6.7 Any...
11
by: randomtalk | last post by:
hi, i have the following recursive function (simplified to demonstrate the problem): >>> def reTest(bool): .... result = .... if not bool: .... reTest(True) .... else: .... print...
9
by: Petr Vileta | last post by:
Hi, I'm new here and excuse me if this question was be here earlier. I have a simple code <html><body> <?php <?php $link = mysql_connect("localhost", "user", "password") or die("Grr: " ....
9
by: tai | last post by:
Hi. I'm looking for a way to define a function that's only effective inside specified function. Featurewise, here's what I want to do: bar_plugin_func = function() { ...; setTimeout(...);...
10
by: Matthew Wilson | last post by:
Lately, I've been writing functions like this: def f(a, b): assert a in assert b in The point is that I'm checking the type and the values of the parameters.
11
by: kennthompson | last post by:
Trouble passing mysql table name in php. If I use an existing table name already defined everything works fine as the following script illustrates. <?php function fms_get_info() { $result =...
9
by: JRough | last post by:
I'm getting the error message that the parameter passed to the function is not a valid resource. The parameter is $result and it is from a query in a switch statement. What do I have to do to get...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.