454,964 Members | 1,206 Online
Need help? Post your question and get tips & solutions from a community of 454,964 IT Pros & Developers. It's quick & easy.

# Divide by zero erro

 P: 5 Hi all, I have a query: SELECT Data.Region, Data.Dept, Data.Year, Data.Month, Data.Week, Data.Elapsed, Data.[Wp Comp Sales \$] AS WpComp, Data.[Cp Comp Sales \$] AS CpComp, IIf([LyNumWks]=53 And [Lywk]="JAN,WK4",[LyComp]/2,[LyComp]) AS AdjLyComp, round(([Wp Comp Sales \$]-IIf([LyNumWks]=53 And [Lywk]="JAN,WK4",[LyComp]/2,[LyComp]))/IIf([LyNumWks]=53 And [Lywk]="JAN,WK4",[LyComp]/2,[LyComp]),6) AS WpVarLy, round(([Cp Comp Sales \$]-IIf([LyNumWks]=53 And [Lywk]="JAN,WK4",[LyComp]/2,[LyComp]))/IIf([LyNumWks]=53 And [Lywk]="JAN,WK4",[LyComp]/2,[LyComp]),6) AS CpVarLy FROM ((Data INNER JOIN Years ON Data.Year=Years.Year) INNER JOIN LyWk ON (Data.Week=LyWk.TyWk) AND (Data.Year=LyWk.TyYear) AND (Data.Region=LyWk.Region) AND (Data.Dept=LyWk.Dept)) INNER JOIN LYears ON Years.Year=LYears.Year; essentially it uses iif to divide by two is the row is the last week of a 53 week year. then, I use the same iif to create a percent to last year's value. Now, I want to take an average of the percent to last year. But, when I try to do anything (sum, avg, etc) with the data from this query I get a messagebox that says "Divide by zero." The parent query runs fine. I'm really stumped! This is my second query: SELECT DataAdjLy.Region, Sum(DataAdjLy.WpVarLy) AS WpVarLy FROM DataAdjLy GROUP BY DataAdjLy.Region; As you acn see it is very simple. This query uses the first query (above) and produces the error. The first queyr does not produce the error. Jan 24 '08 #1