By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,964 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 446
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
Jan 28 '08 #2

jaxjagfan
Expert 100+
P: 254
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"
Jan 28 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.