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

G

Guest

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?
 
G

Guest

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.
 
G

Guest

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 said:
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 said:
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?
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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!

Jerry Whittle said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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!

Jerry Whittle said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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!

Jerry Whittle said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.



Jerry Whittle said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.


Georgia girl said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.

Jerry Whittle said:
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.


Georgia girl said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hey, the .000001 worked! Thank you!

Georgia girl said:
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.

Jerry Whittle said:
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.


Georgia girl said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nz function 6
Can I use an IIF statement with the NZ function? 2
#Error while trying to create DateValue in Access 0
using nz function to rid #error msg in query 5
Data Type mismatch in a query that uses a calculated field from another query 4
Aggregate Function Error 0
Nz(null values in queries, 0) 2
#Error in query 4

Top