divide by zero error: divide\mode by zero is invalid.

Resolving The Problem

Users wishing to avoid divide by zero errors may try to use the following expression to ensure that the value of B is always non-zero:
if (B <> 0) then (A/B) else (0)

This will work with databases like dBASE where all expressions are executed locally by Impromptu. However, even though Impromptu cannot send the entire conditional expression to a true SQL database server, it will still send those parts that it can -- in this case the expression (A/B) to the server for execution. The result is inserted into the overall expression, so the database will still report a divide by zero error and stop fetching records. In order to avoid divide by zero error, you must voice a conditional expression:
(if (B <> 0) then (A) else( 0)) / (if (B <> 0) then (B) else (1))

Since the division now includes a condition expression which must be executed locally, the division will also be executed locally.

If you are still receiving the error after making the changes to your calculation, it may be because there are NULL values in your data.

An ODBC trace may reveal the following:

EXAMPLE:

SQLError
NULL
NULL
0x0101000E
[5]01S01
89
[57][Microsoft][ODBC Microsoft Access 97 Driver]Error in row
512
57
SQL_SUCCESS
SQLError
NULL
NULL
0x0101000E
[5]22012
21
[67][Microsoft][ODBC Microsoft Access 97 Driver]Division by zero (null)
512
67
SQL_SUCCESS
SQLError
NULL
NULL
0x0101000E
[5]00000
35527920
[0]
512
0
SQL_NO_DATA_FOUND

If this is the case, add a calculation to the columns which may contain NULLs to replace the NULLS with zeros. The calculation should look like this:

if columnname is missing then 0 else columnname

Similarly, you can change the conditions to "is missing" instead of "is not missing"

( if ( (B = 0) or (B is missing) ) then (0) else (A) ) / ( if ( (B = 0) or (B is missing) ) then (1) else (B) )

Scenario 1:

Let's say B = 0

( if ( (B = 0) or (B is missing) ) then (0) else (A) )
First condition is true so A = 0

( if ( (B = 0) or (B is missing) ) then (1) else (B) )
First condition is true so B= 1

Result: A/B = 0/1

Scenario 2:

Let's say B = NULL

( if ( (B = 0) or (B is missing) ) then (0) else (A) )
Second condition is true so A = 0

( if ( (B = 0) or (B is missing) ) then (1) else (B) )
Second condition is true so B= 1

Result: A/B = 0/1

Scenario 3:

Let's say B = 2

( if ( (B = 0) or (B is missing) ) then (0) else (A) )
First and second conditions are false A = A

( if ( (B = 0) or (B is missing) ) then (1) else (B) )
First and second conditions are false so B= B

Result: A/B