?
Solved

Divide by Zero Error

Posted on 2005-04-25
6
Medium Priority
?
261 Views
Last Modified: 2008-03-03
Hi All,

I have a query that runs two subqueries to get a percentage.  The SQL text is below.  I need to find a way to make the query just return zero if the divisor will be equal to zero.

With the line below I am getting the following error...

"IIF Function without () in query expression 'iif(select count(*) from datefilter_reports where takeoff_check =1) = 0.'"

Thanks!

G

 iif(select count(*) from datefilter_reports where takeoff_check =1) = 0,0, ((select count(*) from datefilter_reports where takeoff_methodology_RD =1)/(select count(*) from datefilter_reports where takeoff_check = 1)) AS AvgOfTakeoff_Methodology_RD
0
Comment
Question by:graysoc
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:Nestorio
ID: 13857907
Try this,

iif((select count(*) from datefilter_reports where takeoff_check =1) = 0,0, ((select count(*) from datefilter_reports where takeoff_methodology_RD =1)/(select count(*) from datefilter_reports where takeoff_check = 1)) AS AvgOfTakeoff_Methodology_RD
0
 
LVL 1

Author Comment

by:graysoc
ID: 13857989
That got rid of the () error, but now I have a syntax error, missing operator.  I was getting the same error before.  Maybe this just won't work with subqueries?

G
0
 
LVL 16

Accepted Solution

by:
Nestorio earned 1000 total points
ID: 13858065
Try this:

select
sum(iif(takeoff_methodology_RD =1, 1, 0))  as a, sum(iif(takeoff_check =1, 1, 0)) as b, iif(b=0, 0, a / b) as AvgOfTakeoff_Methodology_RD
from  datefilter_reports
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:graysoc
ID: 13858131
Perfect.  Thank you.

Grayson
0
 
LVL 1

Author Comment

by:graysoc
ID: 13858146
One question...  How can I make the A and B not visible in the query?  Whatever results return on the query will end up in an excel sheet.

G
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 13858496
This should be working,

select
iif(sum(iif(takeoff_check =1, 1, 0))=0, 0, sum(iif(takeoff_methodology_RD =1, 1, 0)) / sum(iif(takeoff_check =1, 1, 0))) as AvgOfTakeoff_Methodology_RD
from  datefilter_reports
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Implementing simple internal controls in the Microsoft Access application.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question