Divide by zero erro

[JenavaS]

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.

[sierra7]

Hi
I haven't got time to analyse your code in the first query but you said it worked anyway . . .

What I noticed was "Sum(DataAdjLy.WpVarLy) AS WpVarLy"

I would try "Sum(DataAdjLy.WpVarLy) AS SWpVarLy" changing the output fieldname slightly because I think Access gets confused if you use a field name to hold something which is blatently not the field i.e. 'WpVarLy'

S7

[jaxjagfan]

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.

Some of your column values are being returned as 0. Within the IIF you will need to include some thing like this:

IIF(MyValue = 0,0,MyFormula)

If those rows are not needed then in the main portion of the SELECT WHERE clause put:
Where MyFieldValue <> 0

Is this the only way you have access to the data? It seems to be in a more complex structure than is necessary. If you are trying to get the weeks difference between 2 dates then use a DateDiff function.

DateDiff("ww", date1, date2)

I see this repeated in your post - [LyNumWks]=53 And [Lywk]="JAN,WK4"

If you are only calcualting values where LyNumWks=53 and Lywk = "JAN,WK4", then put those in the WHERE clause

Select MyFormula1, MyFormula2
From MyDataSource
Where [LyNumWks]=53 And [Lywk]="JAN,WK4"