I am receiving an #Error in my query even after using Nz function

[Georgia girl]

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?

 

[Jerry Whittle]

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.

 

[Georgia girl]

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.

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.

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?

 

[Jerry Whittle]

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.

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.

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.

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?

 

[Georgia girl]

I only have the problem it seems in this query when dividing by zero. Please
see 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!

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.

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.

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.


:

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?

 

[Georgia girl]

I only have the problem it seems in this query when dividing by zero. Please
see 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!

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.

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.

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.


:

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?

 

[Georgia girl]

I seem to only have 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!

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.

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.

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.


:

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?

 

[Georgia girl]

I seem to only be having the problem when it is a division by zero. Please
see 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.

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.

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.

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.


:

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?

 

[Georgia girl]

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.

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.

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.

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.


:

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?

 

[Jerry Whittle]

Looks like a Whoops while posting the SQL. ;-)

You have numerous places where you are doing division. If any of them hit a
0, you will have an error. You need to ensure no dividing by zero anywhere OR
there are no zeros. One cheat is to add the divisor by something like
..000001.

I also see that you are using the CDate function. This will cause problems
if you hit any data that can not be evaluated as a date. I use the IsDate
function first to make sure that the string can be evaluated as a date before
converting it with CDate.

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

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.

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.

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.

:

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.


:

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?

 

[Georgia girl]

Yes, sorry about the multiple entries. The post was showing the page timed
out though obviously it didn't.

I'll try the .000001 method. There is no way to ensure that there is never
a zero. And when there is info, I need it divided by zero to get the
calculation I'm looking for.

There's gotta be a way to code it to where it will except it, it would seem
to me.

Thanks for your help.

Looks like a Whoops while posting the SQL. ;-)

You have numerous places where you are doing division. If any of them hit a
0, you will have an error. You need to ensure no dividing by zero anywhere OR
there are no zeros. One cheat is to add the divisor by something like
.000001.

I also see that you are using the CDate function. This will cause problems
if you hit any data that can not be evaluated as a date. I use the IsDate
function first to make sure that the string can be evaluated as a date before
converting it with CDate.

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

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.

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.

:

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.

:

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.


:

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?

 

[Georgia girl]

Hey, the .000001 worked! Thank you!

Yes, sorry about the multiple entries. The post was showing the page timed
out though obviously it didn't.

I'll try the .000001 method. There is no way to ensure that there is never
a zero. And when there is info, I need it divided by zero to get the
calculation I'm looking for.

There's gotta be a way to code it to where it will except it, it would seem
to me.

Thanks for your help.

Looks like a Whoops while posting the SQL. ;-)

You have numerous places where you are doing division. If any of them hit a
0, you will have an error. You need to ensure no dividing by zero anywhere OR
there are no zeros. One cheat is to add the divisor by something like
.000001.

I also see that you are using the CDate function. This will cause problems
if you hit any data that can not be evaluated as a date. I use the IsDate
function first to make sure that the string can be evaluated as a date before
converting it with CDate.

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

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.

:

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.

:

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.

:

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.


:

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?