Doyce,
Find every place you have a field in a denominator and wrap the entire expression in something like:
IIf([My Denominator Expression]=0, 0, [My Numerator expression]/[My Denominator Expression])
Also, field names like SLSUOM_29 and [Part Sales].SLSCNV_29 seem to suggest an un-normalized table structure.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 5 Nov 2015 12:36:36 -0800
Subject: [MS_AccessPros] Division by Zero
Hello friends,
I have a report that worked previously. Now when I preview the report, I get the first 9 pages then I get an error that says "Division by Zero" twice and then it stops in the middle of page 10. The report is based on this query:
SELECT [Part Sales].PRTNUM_29, [Part Sales].PMDES1_29, [Part Sales].SLSUOM_29, [Part Sales].SLSCNV_29, [Part Master].CSTCNV_01, [Part Master].COST_01, [Part Sales].PRICE_29, Round(([PRICE_29]-([COST_01]/Nz([CSTCNV_01],1)))/[PRICE_29]*100,1) AS [GP%], Round((([Cost_01]/0.8)/Nz([SLSCNV_29],1)),2) AS [New Price], IIf([Price_29]>[New Price],[Price_29],[New Price]) AS [New Price1], [Price Breaks].PRICE_88, IIf(IsNull([price_88]),"Not Trade Part",CDbl(Round((([Cost_01]/0.6)/Nz([SLSCNV_29],1)),2))) AS [OSS Price], IIf(IsNull([price_88]),Null,Round(([PRICE_88]-([COST_01]/Nz([CSTCNV_01],1)))/Nz([PRICE_88],1)*100,1)) AS [OSSGP%]
FROM ([Part Sales] INNER JOIN [Part Master] ON [Part Sales].PRTNUM_29 = [Part Master].PRTNUM_01) LEFT JOIN [Price Breaks] ON [Part Sales].PRTNUM_29 = [Price Breaks].PRTNUM_88
WHERE ((([Part Sales].PRICE_29)<>0) AND ((Round(([PRICE_29]-([COST_01]/Nz([CSTCNV_01],1)))/[PRICE_29]*100,1))<20)) OR ((([Part Sales].PRICE_29)<>0) AND ((Round(([PRICE_29]-([COST_01]/Nz([CSTCNV_01],1)))/[PRICE_29]*100,1))>=20) AND ((IIf(IsNull([price_88]),Null,Round(([PRICE_88]-([COST_01]/Nz([CSTCNV_01],1)))/Nz([PRICE_88],1)*100,1)))<40 Or (IIf(IsNull([price_88]),Null,Round(([PRICE_88]-([COST_01]/Nz([CSTCNV_01],1)))/Nz([PRICE_88],1)*100,1))) Is Null));
There are no calculated controls on the report and the query runs without error. I'm stumped. I'm sure there is a problem with a couple of records but how do I find those records?
Doyce
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar