RE: I am receiving an #Error in my query even after using Nz funct

I seem to only be having the problem when I am dividing by zero. Please see
the SQL below:

SELECT IIf([DepartTimeStamp] Is Not Null,DateValue([DepartTimeStamp])) AS
WorkDate1, tblTrailers.TrailerId, Count(tblTrailers.TrailerId) AS
CountOfTrailerId, tblTrailers.TrailerNumber, Sum(tblProduct.MbolWeight) AS
SumOfMbolWeight, Sum(IIf([UnitOfMeasure]="Cartons",[MbolWeight],0)) AS
CartonWeight, Sum(IIf([UnitOfMeasure]="Cartons",[RcvdQty],0)) AS Cartons,
[CartonWeight]/[Cartons] AS AvgWtPerCtn,
Sum(IIf([UnitOfMeasure]="Skids",[MbolWeight],0)) AS SkidWeight,
Sum(IIf([UnitOfMeasure]="Skids",[RcvdQty],0)) AS Skids, [SkidWeight]/[Skids]
AS AvgWtPerSkid, Sum(IIf([UnitOfMeasure]="Irregulars",[MbolWeight],0)) AS
IrregularWeight, Sum(IIf([UnitOfMeasure]="Irregulars",[RcvdQty],0)) AS
Irregulars, [SumOfMbolWeight]-([CartonWeight]+[SkidWeight]) AS
AvgWtPerIrregularWeight,
([CartonWeight]+[IrregularWeight])/([Cartons]+[Irregulars]) AS
[AvgWtPer"Carton"], Nz([AvgWtPerSkid])/Nz([AvgWtPerCtn]) AS CasesPerSkid,
[CartonWeight]/[SumOfMbolWeight] AS [Carton%], [SkidWeight]/[SumOfMbolWeight]
AS [Skid%], ([Skid%]+[Carton%])-1 AS [Irregulars%]
FROM tblTrailers INNER JOIN (((tblProduct LEFT JOIN tblUnitOfMeasure ON
tblProduct.RcvdType = tblUnitOfMeasure.UomId) LEFT JOIN tblDestinations ON
tblProduct.Destination = tblDestinations.DestinationId) LEFT JOIN
tblCompanies ON tblDestinations.DestinationCompany = tblCompanies.CompanyId)
ON tblTrailers.TrailerId = tblProduct.InboundTrailer
GROUP BY IIf([DepartTimeStamp] Is Not Null,DateValue([DepartTimeStamp])),
tblTrailers.TrailerId, tblTrailers.TrailerNumber, tblTrailers.Direction
HAVING (((IIf([DepartTimeStamp] Is Not
Null,DateValue([DepartTimeStamp])))>=CDate([Start Date:]) And
(IIf([DepartTimeStamp] Is Not Null,DateValue([DepartTimeStamp])))<=CDate([End
Date:])) AND ((tblTrailers.Direction)="IN"));

Thanks again.

"Jerry Whittle" wrote:

I was interpreting what you said as the NZ happening after the division. You
have to make sure any possibility of dividing by zero never happens.

Please post the entire SQL so we can look at it in toto.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Georgia girl" wrote:

There will always be instances where Cartons = 0 and RcvdQty = 0. Isn't that
the purpose of the Nz function or IIf Is Null expression??? Or am I missing
something here? (It usually works...)

Thanks.

"Jerry Whittle" wrote:

You are dividing by Cartons and this will be 0 if the UnitOfMeasure is not
cartons. Therefore you will get a divide by zero error.

Cartons: Sum(IIf([UnitOfMeasure]="Cartons",[RcvdQty],0))

You would be better off dividing by the sum of RcvdQty and putting in
"Cartons" in the criteria under the UnitOfMeasure field. Also you should
ensure that Sum([RcvdQty]) never equals zero as you'll still have the same
problem.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Georgia girl" wrote:

I have a query that takes my CartonWeight - CartonWeight:
Sum(IIf([UnitOfMeasure]="Cartons",[MbolWeight],0)) and divides it by my
Cartons: Sum(IIf([UnitOfMeasure]="Cartons",[RcvdQty],0)) to get my
AvgWtPerCtn:AvgWtPerCtn: [CartonWeight]/[Cartons].

It seems any way that I write it, I still get an #Error in the query for the
records that do not have current data.

I have used an IIf statement, the Nz function, and even ran another query
from that query utilizing both the IIf statement and Nz function, typing in
AvgWtPerCnt1: IIf(Nz([AvgWtPerCtn])="0",0,[AvgWtPerCtn]) and I'm still
receiving an #Error in the query.

Any ideas?

.

Relevant Pages
Quantcast