# Thread: Calculation in report "Divide by 0" problem

1. Registered User
Join Date
Jan 2003
Posts
126

## Unanswered: Calculation in report "Divide by 0" problem

Hi all,

I have been working with this particular report for a bit. I have a case where I need to divide two numbers... every now and again it's 0/something or 0/0, which gives me and error.

How can I get around this???

example:

value1 = 2
value2 = 4
value3 = value2 / value1

value3 would = 2

another example

value1 = 2
value2 = 0
value3 = value2 / value1

value3 would = Divide by 0

can I do some kind of case statement? I tried an iif:

=IIf(Sum([BACKENDRETURNSTOTAL])>0,Sum([BACKENDRETURNSTOTAL]/[BACKENDRETURNS]),0)

and I get an overflow...

I want to just put a 0 in value3 if it can't be divided.

Any help would be great!

2. Registered User
Join Date
Jan 2003
Posts
15
Value3=IIF(([Value2])=0,0,([Value1]/[Value2]))

You might want to play with the Nz command as well. works really well with dealing with 0 errors.

3. Registered User
Join Date
Jan 2003
Posts
126

## IT WORKED!

That took care of the problem, I sort of had that statment in there at one time!

Thank you for the help!!!!

4. Registered User
Join Date
Jan 2003
Posts
15
Pleasure

5. Registered User
Join Date
Jul 2003
Posts
42

## Division By Zero in Query Expression

I am trying to run an expression in a query to get the sum of certain values. When one of those is zero I get a divide by zero error. I tried doing the NZ function but cant figure it out. I am fairly new to working with numbers in databases, I am used to working with text type dbs and am struggling and would like some help if possible.

Here are the items I am working with:

TA/QY FIELD
CR00 Current Std Cost
CR00 12 Mo Usage (cost)
CR00 Avg Mo Annual Usage (cost)
TA01 Proposed Std Cost

The sums are being developed from a two queries below QY01 (\$Query_CROO-Sum Cost Data) and QY02 (\$Query_CR00 - Saving Cost Data)

QY01 No. Purchased per Year = Current Std Cost – 12 Mo Usage
Query Expression: NoPurAnnually: Sum([TA_CR00 - Cost Reduction Main Table]![12 Mo Annual Usage]/[TA_CR00 - Cost Reduction Main Table]![Current STD Cost])

QY01 No. Purchased per Month = Current Std Cost – Avg Mo Annual Usage
Query Expression: NoPurMonthly: Sum([TA_CR00 - Cost Reduction Main Table]![Avg Mo Annual Usage]/[TA_CR00 - Cost Reduction Main Table]![Current STD Cost])

QY01 Estimated New Year Cost = Proposed Std Cost – 12 Mo Usage
Query Expression: EstNewYrCost: Sum([12 Mo Annual Usage]/[Proposed STD Cost])

QY01 Estimated New Mo Cost = Avg Mo Annual Usage – Proposed Std Cost
Query Expression: EstNewMoCost: Sum([Avg Mo Annual Usage]/[Proposed STD Cost])

QY02 Estimated Annual Savings = pulls from the QY01 query above titled: \$Query CROO-Sum Cost Data
Query Expression: EstAnnualSvg: Sum([\$Query_CR00 - Sum Cost Data]![12 Mo Annual Usage]-[\$Query_CR00 - Sum Cost Data]![EstNewYrCost])

QY02 Estimated Monthly Savings = pulls from the QY01 query above titled: \$Query CROO-Sum Cost Data
Query Expression: EstMonthlySvg: Sum([\$Query_CR00 - Sum Cost Data]![Avg Mo Annual Usage]-[\$Query_CR00 - Sum Cost Data]![EstNewMoCost])

The problem is that Proposed Std Cost could be zero (default in the table sets it to zero in the field) while people are working on it OR it could be blank if it was deleted (which can be corrected with a filter) however I would rather it see the all the data. I cant get reports to work without it. The current above queries cant work unless data in that field is not zero.

Questions:
· Can someone please give me an example of a NZ query and tell me where it needs to go in the queries above in order for the reports to work.
· Is there a way to combine these queries into one single query?
· Is there a way to get the query data back into the main table once the query generates the numbers?

6. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
taking this one as an example
Code:
`Query Expression: EstNewYrCost: Sum([12 Mo Annual Usage]/[Proposed STD Cost])`
change to:
Code:
`Query Expression: EstNewYrCost: iif([Proposed STD Cost] = 0, "No Proposed STD Cost", Sum([12 Mo Annual Usage]/[Proposed STD Cost]))`
....sorry to say that i'm not a fan of table/field/form/control/query names that contain spaces or hyphens.

izy

7. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
"Is there a way to get the query data back into the main table once the query generates the numbers?"

why would you want to do this when you can generate the calcs any time with the query?

you would run the risk of someone changing (e.g.) a [Proposed Standard Cost], but your [EstNewYrCost] staying the same.

yes of course you could re-run the query to update [EstNewYrCost], but to be CERTAIN you would have to do it every time you look at the table = even more of a hassle than generating the calcs with the query when you need the calcs.

to answer your question strictly: yes. (almost?) anything you can do in a query can find it's way into a table (experiment with update queries after modifying the table structure to have fields for a calc). but do it for curiosity, not for real.

izy
Last edited by izyrider; 07-18-03 at 14:50.

8. Registered User
Join Date
Jul 2003
Posts
42

## fixed - thank you

Thanks to all for your previous help. I was able to get the divide by zero error corrected. I’m fairly new to this so the naming convention using spacing is wrong, but I will need to go with what I have for now and change naming convention as I get the opportunity and for the future stuff. Curious, why is name spacing not suggested?

9. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
myTextBox.value
works fine but
my Text Box.value
doesn't

either you dedicate your life to finding "[" and "]" on your keyboard, or you eliminate the spaces (and hyphens)

[reading] AND [debugging a long SQL query littered] WITH [unnecessary square brackets] AND [spaces is more difficult]

izy

10. Registered User
Join Date
Aug 2003
Posts
1

## same problem, help!

dear izyrider,
hi, i got the same problem - Divide by zero error encountered.

herewith i enclosed the store procedure as an attachment.

thank you very much indeed.

regards,
Catcyc

Originally posted by izyrider
myTextBox.value
works fine but
my Text Box.value
doesn't

either you dedicate your life to finding "[" and "]" on your keyboard, or you eliminate the spaces (and hyphens)

[reading] AND [debugging a long SQL query littered] WITH [unnecessary square brackets] AND [spaces is more difficult]

izy

11. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
your only division is in the --cast part of the code:
Code:
```	--cast(
Sum(Case
LOGICALTEST1  then 1
Else 0
End) * 100 /
Sum(Case
LOGICALTEST2 then 1
else 0
end)
--as numeric(3,2))```
you are clearly setting yourself up for a fall with the second else 0

could you consider something like (pseudo code)
Code:
```if (LOGICALTEST1 AND LOGICALTEST2) then
--cast(
...etc etc as before
--as numeric(3,2))
else
0
endif```
izy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•