Division by zero error in a report...

B

Brook

good day,

I have a cost/profit report that I use based on my qryinventory. when I run
my report I am getting the above error... below is my SQL if that will help
solve the problem..

I'm stumped!

Brook

SELECT tblinventory.shipmentrequest, tblinventory.orderid,
tblinventory.OrderNumber, tblinventory.Ordertype, tblinventory.ordnum,
tblinventory.Client, tblinventory.Customer, tblinventory.clientcontact,
tblinventory.RugProdStatus, tblinventory.SalesStatus,
tblinventory.SalesStatusUpdatedon, tblinventory.OrderDate,
tblinventory.DueDate, tblinventory.ConfirmationDate,
tblinventory.CustomNumber, tblinventory.DesignName,
tblinventory.DesignNameMeaning, tblinventory.DesignNumber,
tblinventory.CarpetNumber, tblinventory.OrderSizeWidthft,
tblinventory.OrderSizeLengthft, Int([OrderSizeWidthft]) AS WholeNumberWidth,
Int([OrderSizeLengthft]) AS WholeNumberLength,
Round(([OrderSizeWidthft]-Int([OrderSizeWidthft]))*12) AS widthfraction,
Round(([OrderSizeLengthft]-Int([OrderSizeLengthft]))*12) AS lengthfraction,
[ordersizewidthft] & " ' " & " x " & [ordersizelengthft] & " ' " AS [Size],
[WholeNumberWidth] & "'" & [widthfraction] & '"' & " x " &
[WholeNumberlength] & "'" & [Lengthfraction] & '"' AS SizeFeet,
Round([Ordersizewidthft]*[ordersizelengthft],2) AS Sqfeet,
Round([SqFeet]*0.092903,3) AS OrderM2, tblinventory.BaseColour,
tblinventory.FieldDesignColours, tblinventory.[SilkViscose Colours],
[Basecolour] & "," & [FieldDesignColours] & "," & [SilkViscose Colours] AS
Colours, tblinventory.[%Silk], tblinventory.[%Viscose], tblinventory.Quality,
tblinventory.QualityName, tblinventory.Contents, tblinventory.Fringe,
tblinventory.Wash, tblinventory.SpecialInstructions,
tblinventory.shipmentrequestdate, tblinventory.[Notes/Comments],
tblinventory.RecdSizeWidthft, tblinventory.RecdSizeLengthft,
Round([RecdSizeWidthft]*[RecdSizeLengthft],3) AS RecdSqFt,
Round([RecdSqFt]*0.092903,3) AS RecdM2, [SqFeet]-[RecdSqFt] AS SqFeetDiff,
tblinventory.PackingListcmWidth, tblinventory.PackingListcmLength,
Round(([PackingLIstcmWidth]/100)/0.3048,3) AS PackingListWidthft,
Round(([PackingListcmLength]/100)/0.3048,2) AS PackingListLengthft,
Round(NZ([PackingListcmWidth]*[PackingListcmLength],3)) AS ttlcm2shipped,
Round(NZ([ttlcm2shipped]/10000,2)) AS ttlm2Shipped,
Round([ttlm2shipped]*10.76,3) AS ttlft2shipped,
Round([PriceM2Shipped]/10.76,3) AS Priceft2Shipped,
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped,
Round([NWPackingListWeight]*[NepalExportCoInvoiceRate],2) AS
ShippingCostBasedOnNWWeights,
Round([NepalExportCoShippingCost]-[NWTotalPackingListShippingEst],2) AS
TTNWShippingEstDifference,
Round([TTNWShippingEstDifference]*[PercentofTotalM2Shipped],2) AS
ExtraShippingCostFromNepal,
Round([ExtraShippingCostfromNepal]+[ShippingCostBasedOnNWWeights],2) AS
TtlshippingcostfromNepal, tblinventory.ReceivingNotes,
tblinventory.NepalExportCoShipmentDate,
tblinventory.NepalExportCoShipmentInvoiceNo,
tblinventory.NepalExportCoInvoiceWeight,
tblinventory.NepalExportCoInvoiceRate,
tblinventory.NepalExportCoShippingCost, tblinventory.NWPackingListWeight,
tblinventory.NWTotalPackingListWeight,
tblinventory.NWTotalPackingListShippingEst, tblinventory.BWICargoDate,
tblinventory.BWICargoCompany, tblinventory.BWICargoFee,
Round([PercentofTotalM2Shipped]*[BWICargoFee],2) AS BWICargoFeeDisb,
tblinventory.CustomsEntryDate, tblinventory.CustomsEntryCompany,
tblinventory.CustomsEntryFees,
Round([PercentofTotalM2Shipped]*[CustomsEntryFees],2) AS
CustomsEntryFeesDisb, tblinventory.CustomsEntryBondFee,
Round([PercentofTotalM2Shipped]*[CustomsEntryBondFee],2) AS
CustomsEntryBondFeeDisb, tblinventory.CustomsEntryTaxes,
Round([PercentofTotalM2Shipped]*[CustomsEntryTaxes],2) AS
CustomsEntryTaxesDisb, tblinventory.PickupDeliveryFee,
Round([PercentofTotalM2Shipped]*[PickupDeliveryFee],2) AS
PickupDeliveryFeeDisb, tblinventory.PriceM2Shipped,
tblinventory.TotalM2Shipped, tblinventory.WeightM2,
tblinventory.PricePerSqFoot, tblinventory.strikeofffee,
tblinventory.shippingmethod, tblinventory.shippingrate,
Round([sqfeet]*[shippingrate],2) AS ShippingCost,
tblinventory.actualshippingcost,
Round([BWICargoFeeDisb]+[CustomsEntryFeesDisb]+[CustomsEntryBondFeeDisb]+[CustomsEntryTaxesDisb]+[PickupDeliveryFeeDisb],2)
AS TotalImportingFees, Round([ttlm2shipped]*[PriceM2Shipped],2) AS
TotalRugProductionCostPreDisc, tblinventory.NepalDiscountPercent,
[TotalRugProductionCostPreDisc]*[NepalDiscountPercent] AS DiscAmt,
Round([TotalRugProductionCostPreDisc]-[TotalRugProductionCostPreDisc]*[NepalDiscountPercent],2)
AS TotalRugProductionCost,
Round([TotalRugProductionCost]+[ttlshippingcostfromNepal],2) AS
TotalRugCostProdShipping,
Round([TotalRugCostProdShipping]+[TotalImportingFees],2) AS
TotalRugCostProdUSEntry,
Round([TotalImportingFees]+[TtlshippingcostfromNepal],2) AS
TotalShippingImportFees, Round([SqFeet]*[Pricepersqfoot]+[strikeofffee],2) AS
totalprice, Round([OrderM2]*[PriceM2Shipped],2) AS totalM2Price,
[ttlm2shipped]*[PriceM2Shipped] AS totalM2PriceReceived, "<center>" &
[DesignName] & "<BR>" & [DesignNumber] & "<BR>" & [SizeFeet] & "<BR>" &
[Colours] & "<BR>" & [Quality] & " Knots" & "<BR>" & [Contents] & "<BR>" &
[Notes/Comments] & "</center>" AS comment,
([TotalPrice]-[TotalRugCostProdUSEntry]) AS TotalProfit,
tblinventory.PrintLabel, tblinventory.PrintOrder,
tblinventory.NepaltoKarmaRugStatus, tblinventory.NomadWeaversInvoiceDate,
tblinventory.NomadWeaversInvoiceM2, tblinventory.[NomadWeaversInvoice#],
([PriceM2Shipped]*[NomadWeaversInvoiceM2]) AS NepalInvoicePrice
FROM tblinventory;
 
K

Ken Snell \(MVP\)

It's probably occurring in this calculated field:

IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped,

You'll need to do something in this expression to handle when TotalM2Shipped
is zero.
 
B

Brook

Ofer...

Thank you so much... I have been racking my brain for days!

Brook

Ofer Cohen said:
Change this
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped

To
IIf([TotalM2Shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped

the iif on the TotalM2Shipped field rather then ttlm2shipped.
--
Good Luck
BS"D


Brook said:
good day,

I have a cost/profit report that I use based on my qryinventory. when I run
my report I am getting the above error... below is my SQL if that will help
solve the problem..

I'm stumped!

Brook

SELECT tblinventory.shipmentrequest, tblinventory.orderid,
tblinventory.OrderNumber, tblinventory.Ordertype, tblinventory.ordnum,
tblinventory.Client, tblinventory.Customer, tblinventory.clientcontact,
tblinventory.RugProdStatus, tblinventory.SalesStatus,
tblinventory.SalesStatusUpdatedon, tblinventory.OrderDate,
tblinventory.DueDate, tblinventory.ConfirmationDate,
tblinventory.CustomNumber, tblinventory.DesignName,
tblinventory.DesignNameMeaning, tblinventory.DesignNumber,
tblinventory.CarpetNumber, tblinventory.OrderSizeWidthft,
tblinventory.OrderSizeLengthft, Int([OrderSizeWidthft]) AS WholeNumberWidth,
Int([OrderSizeLengthft]) AS WholeNumberLength,
Round(([OrderSizeWidthft]-Int([OrderSizeWidthft]))*12) AS widthfraction,
Round(([OrderSizeLengthft]-Int([OrderSizeLengthft]))*12) AS lengthfraction,
[ordersizewidthft] & " ' " & " x " & [ordersizelengthft] & " ' " AS [Size],
[WholeNumberWidth] & "'" & [widthfraction] & '"' & " x " &
[WholeNumberlength] & "'" & [Lengthfraction] & '"' AS SizeFeet,
Round([Ordersizewidthft]*[ordersizelengthft],2) AS Sqfeet,
Round([SqFeet]*0.092903,3) AS OrderM2, tblinventory.BaseColour,
tblinventory.FieldDesignColours, tblinventory.[SilkViscose Colours],
[Basecolour] & "," & [FieldDesignColours] & "," & [SilkViscose Colours] AS
Colours, tblinventory.[%Silk], tblinventory.[%Viscose], tblinventory.Quality,
tblinventory.QualityName, tblinventory.Contents, tblinventory.Fringe,
tblinventory.Wash, tblinventory.SpecialInstructions,
tblinventory.shipmentrequestdate, tblinventory.[Notes/Comments],
tblinventory.RecdSizeWidthft, tblinventory.RecdSizeLengthft,
Round([RecdSizeWidthft]*[RecdSizeLengthft],3) AS RecdSqFt,
Round([RecdSqFt]*0.092903,3) AS RecdM2, [SqFeet]-[RecdSqFt] AS SqFeetDiff,
tblinventory.PackingListcmWidth, tblinventory.PackingListcmLength,
Round(([PackingLIstcmWidth]/100)/0.3048,3) AS PackingListWidthft,
Round(([PackingListcmLength]/100)/0.3048,2) AS PackingListLengthft,
Round(NZ([PackingListcmWidth]*[PackingListcmLength],3)) AS ttlcm2shipped,
Round(NZ([ttlcm2shipped]/10000,2)) AS ttlm2Shipped,
Round([ttlm2shipped]*10.76,3) AS ttlft2shipped,
Round([PriceM2Shipped]/10.76,3) AS Priceft2Shipped,
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped,
Round([NWPackingListWeight]*[NepalExportCoInvoiceRate],2) AS
ShippingCostBasedOnNWWeights,
Round([NepalExportCoShippingCost]-[NWTotalPackingListShippingEst],2) AS
TTNWShippingEstDifference,
Round([TTNWShippingEstDifference]*[PercentofTotalM2Shipped],2) AS
ExtraShippingCostFromNepal,
Round([ExtraShippingCostfromNepal]+[ShippingCostBasedOnNWWeights],2) AS
TtlshippingcostfromNepal, tblinventory.ReceivingNotes,
tblinventory.NepalExportCoShipmentDate,
tblinventory.NepalExportCoShipmentInvoiceNo,
tblinventory.NepalExportCoInvoiceWeight,
tblinventory.NepalExportCoInvoiceRate,
tblinventory.NepalExportCoShippingCost, tblinventory.NWPackingListWeight,
tblinventory.NWTotalPackingListWeight,
tblinventory.NWTotalPackingListShippingEst, tblinventory.BWICargoDate,
tblinventory.BWICargoCompany, tblinventory.BWICargoFee,
Round([PercentofTotalM2Shipped]*[BWICargoFee],2) AS BWICargoFeeDisb,
tblinventory.CustomsEntryDate, tblinventory.CustomsEntryCompany,
tblinventory.CustomsEntryFees,
Round([PercentofTotalM2Shipped]*[CustomsEntryFees],2) AS
CustomsEntryFeesDisb, tblinventory.CustomsEntryBondFee,
Round([PercentofTotalM2Shipped]*[CustomsEntryBondFee],2) AS
CustomsEntryBondFeeDisb, tblinventory.CustomsEntryTaxes,
Round([PercentofTotalM2Shipped]*[CustomsEntryTaxes],2) AS
CustomsEntryTaxesDisb, tblinventory.PickupDeliveryFee,
Round([PercentofTotalM2Shipped]*[PickupDeliveryFee],2) AS
PickupDeliveryFeeDisb, tblinventory.PriceM2Shipped,
tblinventory.TotalM2Shipped, tblinventory.WeightM2,
tblinventory.PricePerSqFoot, tblinventory.strikeofffee,
tblinventory.shippingmethod, tblinventory.shippingrate,
Round([sqfeet]*[shippingrate],2) AS ShippingCost,
tblinventory.actualshippingcost,
Round([BWICargoFeeDisb]+[CustomsEntryFeesDisb]+[CustomsEntryBondFeeDisb]+[CustomsEntryTaxesDisb]+[PickupDeliveryFeeDisb],2)
AS TotalImportingFees, Round([ttlm2shipped]*[PriceM2Shipped],2) AS
TotalRugProductionCostPreDisc, tblinventory.NepalDiscountPercent,
[TotalRugProductionCostPreDisc]*[NepalDiscountPercent] AS DiscAmt,
Round([TotalRugProductionCostPreDisc]-[TotalRugProductionCostPreDisc]*[NepalDiscountPercent],2)
AS TotalRugProductionCost,
Round([TotalRugProductionCost]+[ttlshippingcostfromNepal],2) AS
TotalRugCostProdShipping,
Round([TotalRugCostProdShipping]+[TotalImportingFees],2) AS
TotalRugCostProdUSEntry,
Round([TotalImportingFees]+[TtlshippingcostfromNepal],2) AS
TotalShippingImportFees, Round([SqFeet]*[Pricepersqfoot]+[strikeofffee],2) AS
totalprice, Round([OrderM2]*[PriceM2Shipped],2) AS totalM2Price,
[ttlm2shipped]*[PriceM2Shipped] AS totalM2PriceReceived, "<center>" &
[DesignName] & "<BR>" & [DesignNumber] & "<BR>" & [SizeFeet] & "<BR>" &
[Colours] & "<BR>" & [Quality] & " Knots" & "<BR>" & [Contents] & "<BR>" &
[Notes/Comments] & "</center>" AS comment,
([TotalPrice]-[TotalRugCostProdUSEntry]) AS TotalProfit,
tblinventory.PrintLabel, tblinventory.PrintOrder,
tblinventory.NepaltoKarmaRugStatus, tblinventory.NomadWeaversInvoiceDate,
tblinventory.NomadWeaversInvoiceM2, tblinventory.[NomadWeaversInvoice#],
([PriceM2Shipped]*[NomadWeaversInvoiceM2]) AS NepalInvoicePrice
FROM tblinventory;
Click to expand...
Click to expand...
 
B

Brook

Ofer...

I wanted to thank you again, and ask you how you found the issue so fast
with all the code I posted? I have searched and searched for days...

Any tips for the future?

Thanks,

Brook

Ofer Cohen said:
Change this
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped

To
IIf([TotalM2Shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped

the iif on the TotalM2Shipped field rather then ttlm2shipped.
--
Good Luck
BS"D


Brook said:
good day,

I have a cost/profit report that I use based on my qryinventory. when I run
my report I am getting the above error... below is my SQL if that will help
solve the problem..

I'm stumped!

Brook

SELECT tblinventory.shipmentrequest, tblinventory.orderid,
tblinventory.OrderNumber, tblinventory.Ordertype, tblinventory.ordnum,
tblinventory.Client, tblinventory.Customer, tblinventory.clientcontact,
tblinventory.RugProdStatus, tblinventory.SalesStatus,
tblinventory.SalesStatusUpdatedon, tblinventory.OrderDate,
tblinventory.DueDate, tblinventory.ConfirmationDate,
tblinventory.CustomNumber, tblinventory.DesignName,
tblinventory.DesignNameMeaning, tblinventory.DesignNumber,
tblinventory.CarpetNumber, tblinventory.OrderSizeWidthft,
tblinventory.OrderSizeLengthft, Int([OrderSizeWidthft]) AS WholeNumberWidth,
Int([OrderSizeLengthft]) AS WholeNumberLength,
Round(([OrderSizeWidthft]-Int([OrderSizeWidthft]))*12) AS widthfraction,
Round(([OrderSizeLengthft]-Int([OrderSizeLengthft]))*12) AS lengthfraction,
[ordersizewidthft] & " ' " & " x " & [ordersizelengthft] & " ' " AS [Size],
[WholeNumberWidth] & "'" & [widthfraction] & '"' & " x " &
[WholeNumberlength] & "'" & [Lengthfraction] & '"' AS SizeFeet,
Round([Ordersizewidthft]*[ordersizelengthft],2) AS Sqfeet,
Round([SqFeet]*0.092903,3) AS OrderM2, tblinventory.BaseColour,
tblinventory.FieldDesignColours, tblinventory.[SilkViscose Colours],
[Basecolour] & "," & [FieldDesignColours] & "," & [SilkViscose Colours] AS
Colours, tblinventory.[%Silk], tblinventory.[%Viscose], tblinventory.Quality,
tblinventory.QualityName, tblinventory.Contents, tblinventory.Fringe,
tblinventory.Wash, tblinventory.SpecialInstructions,
tblinventory.shipmentrequestdate, tblinventory.[Notes/Comments],
tblinventory.RecdSizeWidthft, tblinventory.RecdSizeLengthft,
Round([RecdSizeWidthft]*[RecdSizeLengthft],3) AS RecdSqFt,
Round([RecdSqFt]*0.092903,3) AS RecdM2, [SqFeet]-[RecdSqFt] AS SqFeetDiff,
tblinventory.PackingListcmWidth, tblinventory.PackingListcmLength,
Round(([PackingLIstcmWidth]/100)/0.3048,3) AS PackingListWidthft,
Round(([PackingListcmLength]/100)/0.3048,2) AS PackingListLengthft,
Round(NZ([PackingListcmWidth]*[PackingListcmLength],3)) AS ttlcm2shipped,
Round(NZ([ttlcm2shipped]/10000,2)) AS ttlm2Shipped,
Round([ttlm2shipped]*10.76,3) AS ttlft2shipped,
Round([PriceM2Shipped]/10.76,3) AS Priceft2Shipped,
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped,
Round([NWPackingListWeight]*[NepalExportCoInvoiceRate],2) AS
ShippingCostBasedOnNWWeights,
Round([NepalExportCoShippingCost]-[NWTotalPackingListShippingEst],2) AS
TTNWShippingEstDifference,
Round([TTNWShippingEstDifference]*[PercentofTotalM2Shipped],2) AS
ExtraShippingCostFromNepal,
Round([ExtraShippingCostfromNepal]+[ShippingCostBasedOnNWWeights],2) AS
TtlshippingcostfromNepal, tblinventory.ReceivingNotes,
tblinventory.NepalExportCoShipmentDate,
tblinventory.NepalExportCoShipmentInvoiceNo,
tblinventory.NepalExportCoInvoiceWeight,
tblinventory.NepalExportCoInvoiceRate,
tblinventory.NepalExportCoShippingCost, tblinventory.NWPackingListWeight,
tblinventory.NWTotalPackingListWeight,
tblinventory.NWTotalPackingListShippingEst, tblinventory.BWICargoDate,
tblinventory.BWICargoCompany, tblinventory.BWICargoFee,
Round([PercentofTotalM2Shipped]*[BWICargoFee],2) AS BWICargoFeeDisb,
tblinventory.CustomsEntryDate, tblinventory.CustomsEntryCompany,
tblinventory.CustomsEntryFees,
Round([PercentofTotalM2Shipped]*[CustomsEntryFees],2) AS
CustomsEntryFeesDisb, tblinventory.CustomsEntryBondFee,
Round([PercentofTotalM2Shipped]*[CustomsEntryBondFee],2) AS
CustomsEntryBondFeeDisb, tblinventory.CustomsEntryTaxes,
Round([PercentofTotalM2Shipped]*[CustomsEntryTaxes],2) AS
CustomsEntryTaxesDisb, tblinventory.PickupDeliveryFee,
Round([PercentofTotalM2Shipped]*[PickupDeliveryFee],2) AS
PickupDeliveryFeeDisb, tblinventory.PriceM2Shipped,
tblinventory.TotalM2Shipped, tblinventory.WeightM2,
tblinventory.PricePerSqFoot, tblinventory.strikeofffee,
tblinventory.shippingmethod, tblinventory.shippingrate,
Round([sqfeet]*[shippingrate],2) AS ShippingCost,
tblinventory.actualshippingcost,
Round([BWICargoFeeDisb]+[CustomsEntryFeesDisb]+[CustomsEntryBondFeeDisb]+[CustomsEntryTaxesDisb]+[PickupDeliveryFeeDisb],2)
AS TotalImportingFees, Round([ttlm2shipped]*[PriceM2Shipped],2) AS
TotalRugProductionCostPreDisc, tblinventory.NepalDiscountPercent,
[TotalRugProductionCostPreDisc]*[NepalDiscountPercent] AS DiscAmt,
Round([TotalRugProductionCostPreDisc]-[TotalRugProductionCostPreDisc]*[NepalDiscountPercent],2)
AS TotalRugProductionCost,
Round([TotalRugProductionCost]+[ttlshippingcostfromNepal],2) AS
TotalRugCostProdShipping,
Round([TotalRugCostProdShipping]+[TotalImportingFees],2) AS
TotalRugCostProdUSEntry,
Round([TotalImportingFees]+[TtlshippingcostfromNepal],2) AS
TotalShippingImportFees, Round([SqFeet]*[Pricepersqfoot]+[strikeofffee],2) AS
totalprice, Round([OrderM2]*[PriceM2Shipped],2) AS totalM2Price,
[ttlm2shipped]*[PriceM2Shipped] AS totalM2PriceReceived, "<center>" &
[DesignName] & "<BR>" & [DesignNumber] & "<BR>" & [SizeFeet] & "<BR>" &
[Colours] & "<BR>" & [Quality] & " Knots" & "<BR>" & [Contents] & "<BR>" &
[Notes/Comments] & "</center>" AS comment,
([TotalPrice]-[TotalRugCostProdUSEntry]) AS TotalProfit,
tblinventory.PrintLabel, tblinventory.PrintOrder,
tblinventory.NepaltoKarmaRugStatus, tblinventory.NomadWeaversInvoiceDate,
tblinventory.NomadWeaversInvoiceM2, tblinventory.[NomadWeaversInvoice#],
([PriceM2Shipped]*[NomadWeaversInvoiceM2]) AS NepalInvoicePrice
FROM tblinventory;
Click to expand...
Click to expand...
 
O

Ofer Cohen

Hi Brook
I took two steps using the error you specified "Division by zero error in a
report"

1. Look for any part in your SQL that has a devision, lucky for me that you
had only one.

2. In devision you can have
0/2
But you can't have
2/0
And that what the error indicates
And this is why you should do the IIf on the second field and not the first
one.
--
Good Luck
BS"D


Brook said:
Ofer...

I wanted to thank you again, and ask you how you found the issue so fast
with all the code I posted? I have searched and searched for days...

Any tips for the future?

Thanks,

Brook

Ofer Cohen said:
Change this
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped

To
IIf([TotalM2Shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped

the iif on the TotalM2Shipped field rather then ttlm2shipped.
--
Good Luck
BS"D


Brook said:
good day,

I have a cost/profit report that I use based on my qryinventory. when I run
my report I am getting the above error... below is my SQL if that will help
solve the problem..

I'm stumped!

Brook

SELECT tblinventory.shipmentrequest, tblinventory.orderid,
tblinventory.OrderNumber, tblinventory.Ordertype, tblinventory.ordnum,
tblinventory.Client, tblinventory.Customer, tblinventory.clientcontact,
tblinventory.RugProdStatus, tblinventory.SalesStatus,
tblinventory.SalesStatusUpdatedon, tblinventory.OrderDate,
tblinventory.DueDate, tblinventory.ConfirmationDate,
tblinventory.CustomNumber, tblinventory.DesignName,
tblinventory.DesignNameMeaning, tblinventory.DesignNumber,
tblinventory.CarpetNumber, tblinventory.OrderSizeWidthft,
tblinventory.OrderSizeLengthft, Int([OrderSizeWidthft]) AS WholeNumberWidth,
Int([OrderSizeLengthft]) AS WholeNumberLength,
Round(([OrderSizeWidthft]-Int([OrderSizeWidthft]))*12) AS widthfraction,
Round(([OrderSizeLengthft]-Int([OrderSizeLengthft]))*12) AS lengthfraction,
[ordersizewidthft] & " ' " & " x " & [ordersizelengthft] & " ' " AS [Size],
[WholeNumberWidth] & "'" & [widthfraction] & '"' & " x " &
[WholeNumberlength] & "'" & [Lengthfraction] & '"' AS SizeFeet,
Round([Ordersizewidthft]*[ordersizelengthft],2) AS Sqfeet,
Round([SqFeet]*0.092903,3) AS OrderM2, tblinventory.BaseColour,
tblinventory.FieldDesignColours, tblinventory.[SilkViscose Colours],
[Basecolour] & "," & [FieldDesignColours] & "," & [SilkViscose Colours] AS
Colours, tblinventory.[%Silk], tblinventory.[%Viscose], tblinventory.Quality,
tblinventory.QualityName, tblinventory.Contents, tblinventory.Fringe,
tblinventory.Wash, tblinventory.SpecialInstructions,
tblinventory.shipmentrequestdate, tblinventory.[Notes/Comments],
tblinventory.RecdSizeWidthft, tblinventory.RecdSizeLengthft,
Round([RecdSizeWidthft]*[RecdSizeLengthft],3) AS RecdSqFt,
Round([RecdSqFt]*0.092903,3) AS RecdM2, [SqFeet]-[RecdSqFt] AS SqFeetDiff,
tblinventory.PackingListcmWidth, tblinventory.PackingListcmLength,
Round(([PackingLIstcmWidth]/100)/0.3048,3) AS PackingListWidthft,
Round(([PackingListcmLength]/100)/0.3048,2) AS PackingListLengthft,
Round(NZ([PackingListcmWidth]*[PackingListcmLength],3)) AS ttlcm2shipped,
Round(NZ([ttlcm2shipped]/10000,2)) AS ttlm2Shipped,
Round([ttlm2shipped]*10.76,3) AS ttlft2shipped,
Round([PriceM2Shipped]/10.76,3) AS Priceft2Shipped,
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped,
Round([NWPackingListWeight]*[NepalExportCoInvoiceRate],2) AS
ShippingCostBasedOnNWWeights,
Round([NepalExportCoShippingCost]-[NWTotalPackingListShippingEst],2) AS
TTNWShippingEstDifference,
Round([TTNWShippingEstDifference]*[PercentofTotalM2Shipped],2) AS
ExtraShippingCostFromNepal,
Round([ExtraShippingCostfromNepal]+[ShippingCostBasedOnNWWeights],2) AS
TtlshippingcostfromNepal, tblinventory.ReceivingNotes,
tblinventory.NepalExportCoShipmentDate,
tblinventory.NepalExportCoShipmentInvoiceNo,
tblinventory.NepalExportCoInvoiceWeight,
tblinventory.NepalExportCoInvoiceRate,
tblinventory.NepalExportCoShippingCost, tblinventory.NWPackingListWeight,
tblinventory.NWTotalPackingListWeight,
tblinventory.NWTotalPackingListShippingEst, tblinventory.BWICargoDate,
tblinventory.BWICargoCompany, tblinventory.BWICargoFee,
Round([PercentofTotalM2Shipped]*[BWICargoFee],2) AS BWICargoFeeDisb,
tblinventory.CustomsEntryDate, tblinventory.CustomsEntryCompany,
tblinventory.CustomsEntryFees,
Round([PercentofTotalM2Shipped]*[CustomsEntryFees],2) AS
CustomsEntryFeesDisb, tblinventory.CustomsEntryBondFee,
Round([PercentofTotalM2Shipped]*[CustomsEntryBondFee],2) AS
CustomsEntryBondFeeDisb, tblinventory.CustomsEntryTaxes,
Round([PercentofTotalM2Shipped]*[CustomsEntryTaxes],2) AS
CustomsEntryTaxesDisb, tblinventory.PickupDeliveryFee,
Round([PercentofTotalM2Shipped]*[PickupDeliveryFee],2) AS
PickupDeliveryFeeDisb, tblinventory.PriceM2Shipped,
tblinventory.TotalM2Shipped, tblinventory.WeightM2,
tblinventory.PricePerSqFoot, tblinventory.strikeofffee,
tblinventory.shippingmethod, tblinventory.shippingrate,
Round([sqfeet]*[shippingrate],2) AS ShippingCost,
tblinventory.actualshippingcost,
Round([BWICargoFeeDisb]+[CustomsEntryFeesDisb]+[CustomsEntryBondFeeDisb]+[CustomsEntryTaxesDisb]+[PickupDeliveryFeeDisb],2)
AS TotalImportingFees, Round([ttlm2shipped]*[PriceM2Shipped],2) AS
TotalRugProductionCostPreDisc, tblinventory.NepalDiscountPercent,
[TotalRugProductionCostPreDisc]*[NepalDiscountPercent] AS DiscAmt,
Round([TotalRugProductionCostPreDisc]-[TotalRugProductionCostPreDisc]*[NepalDiscountPercent],2)
AS TotalRugProductionCost,
Round([TotalRugProductionCost]+[ttlshippingcostfromNepal],2) AS
TotalRugCostProdShipping,
Round([TotalRugCostProdShipping]+[TotalImportingFees],2) AS
TotalRugCostProdUSEntry,
Round([TotalImportingFees]+[TtlshippingcostfromNepal],2) AS
TotalShippingImportFees, Round([SqFeet]*[Pricepersqfoot]+[strikeofffee],2) AS
totalprice, Round([OrderM2]*[PriceM2Shipped],2) AS totalM2Price,
[ttlm2shipped]*[PriceM2Shipped] AS totalM2PriceReceived, "<center>" &
[DesignName] & "<BR>" & [DesignNumber] & "<BR>" & [SizeFeet] & "<BR>" &
[Colours] & "<BR>" & [Quality] & " Knots" & "<BR>" & [Contents] & "<BR>" &
[Notes/Comments] & "</center>" AS comment,
([TotalPrice]-[TotalRugCostProdUSEntry]) AS TotalProfit,
tblinventory.PrintLabel, tblinventory.PrintOrder,
tblinventory.NepaltoKarmaRugStatus, tblinventory.NomadWeaversInvoiceDate,
tblinventory.NomadWeaversInvoiceM2, tblinventory.[NomadWeaversInvoice#],
([PriceM2Shipped]*[NomadWeaversInvoiceM2]) AS NepalInvoicePrice
FROM tblinventory;
Click to expand...
Click to expand...
Click to expand...
 
B

Brook

Thanks for the explaination!

have a wonderful day



Brook

Ofer Cohen said:
Hi Brook
I took two steps using the error you specified "Division by zero error in a
report"

1. Look for any part in your SQL that has a devision, lucky for me that you
had only one.

2. In devision you can have
0/2
But you can't have
2/0
And that what the error indicates
And this is why you should do the IIf on the second field and not the first
one.
--
Good Luck
BS"D


Brook said:
Ofer...

I wanted to thank you again, and ask you how you found the issue so fast
with all the code I posted? I have searched and searched for days...

Any tips for the future?

Thanks,

Brook

Ofer Cohen said:
Change this
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped

To
IIf([TotalM2Shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped

the iif on the TotalM2Shipped field rather then ttlm2shipped.
--
Good Luck
BS"D


:

good day,

I have a cost/profit report that I use based on my qryinventory. when I run
my report I am getting the above error... below is my SQL if that will help
solve the problem..

I'm stumped!

Brook

SELECT tblinventory.shipmentrequest, tblinventory.orderid,
tblinventory.OrderNumber, tblinventory.Ordertype, tblinventory.ordnum,
tblinventory.Client, tblinventory.Customer, tblinventory.clientcontact,
tblinventory.RugProdStatus, tblinventory.SalesStatus,
tblinventory.SalesStatusUpdatedon, tblinventory.OrderDate,
tblinventory.DueDate, tblinventory.ConfirmationDate,
tblinventory.CustomNumber, tblinventory.DesignName,
tblinventory.DesignNameMeaning, tblinventory.DesignNumber,
tblinventory.CarpetNumber, tblinventory.OrderSizeWidthft,
tblinventory.OrderSizeLengthft, Int([OrderSizeWidthft]) AS WholeNumberWidth,
Int([OrderSizeLengthft]) AS WholeNumberLength,
Round(([OrderSizeWidthft]-Int([OrderSizeWidthft]))*12) AS widthfraction,
Round(([OrderSizeLengthft]-Int([OrderSizeLengthft]))*12) AS lengthfraction,
[ordersizewidthft] & " ' " & " x " & [ordersizelengthft] & " ' " AS [Size],
[WholeNumberWidth] & "'" & [widthfraction] & '"' & " x " &
[WholeNumberlength] & "'" & [Lengthfraction] & '"' AS SizeFeet,
Round([Ordersizewidthft]*[ordersizelengthft],2) AS Sqfeet,
Round([SqFeet]*0.092903,3) AS OrderM2, tblinventory.BaseColour,
tblinventory.FieldDesignColours, tblinventory.[SilkViscose Colours],
[Basecolour] & "," & [FieldDesignColours] & "," & [SilkViscose Colours] AS
Colours, tblinventory.[%Silk], tblinventory.[%Viscose], tblinventory.Quality,
tblinventory.QualityName, tblinventory.Contents, tblinventory.Fringe,
tblinventory.Wash, tblinventory.SpecialInstructions,
tblinventory.shipmentrequestdate, tblinventory.[Notes/Comments],
tblinventory.RecdSizeWidthft, tblinventory.RecdSizeLengthft,
Round([RecdSizeWidthft]*[RecdSizeLengthft],3) AS RecdSqFt,
Round([RecdSqFt]*0.092903,3) AS RecdM2, [SqFeet]-[RecdSqFt] AS SqFeetDiff,
tblinventory.PackingListcmWidth, tblinventory.PackingListcmLength,
Round(([PackingLIstcmWidth]/100)/0.3048,3) AS PackingListWidthft,
Round(([PackingListcmLength]/100)/0.3048,2) AS PackingListLengthft,
Round(NZ([PackingListcmWidth]*[PackingListcmLength],3)) AS ttlcm2shipped,
Round(NZ([ttlcm2shipped]/10000,2)) AS ttlm2Shipped,
Round([ttlm2shipped]*10.76,3) AS ttlft2shipped,
Round([PriceM2Shipped]/10.76,3) AS Priceft2Shipped,
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4)) AS
PercentofTotalM2Shipped,
Round([NWPackingListWeight]*[NepalExportCoInvoiceRate],2) AS
ShippingCostBasedOnNWWeights,
Round([NepalExportCoShippingCost]-[NWTotalPackingListShippingEst],2) AS
TTNWShippingEstDifference,
Round([TTNWShippingEstDifference]*[PercentofTotalM2Shipped],2) AS
ExtraShippingCostFromNepal,
Round([ExtraShippingCostfromNepal]+[ShippingCostBasedOnNWWeights],2) AS
TtlshippingcostfromNepal, tblinventory.ReceivingNotes,
tblinventory.NepalExportCoShipmentDate,
tblinventory.NepalExportCoShipmentInvoiceNo,
tblinventory.NepalExportCoInvoiceWeight,
tblinventory.NepalExportCoInvoiceRate,
tblinventory.NepalExportCoShippingCost, tblinventory.NWPackingListWeight,
tblinventory.NWTotalPackingListWeight,
tblinventory.NWTotalPackingListShippingEst, tblinventory.BWICargoDate,
tblinventory.BWICargoCompany, tblinventory.BWICargoFee,
Round([PercentofTotalM2Shipped]*[BWICargoFee],2) AS BWICargoFeeDisb,
tblinventory.CustomsEntryDate, tblinventory.CustomsEntryCompany,
tblinventory.CustomsEntryFees,
Round([PercentofTotalM2Shipped]*[CustomsEntryFees],2) AS
CustomsEntryFeesDisb, tblinventory.CustomsEntryBondFee,
Round([PercentofTotalM2Shipped]*[CustomsEntryBondFee],2) AS
CustomsEntryBondFeeDisb, tblinventory.CustomsEntryTaxes,
Round([PercentofTotalM2Shipped]*[CustomsEntryTaxes],2) AS
CustomsEntryTaxesDisb, tblinventory.PickupDeliveryFee,
Round([PercentofTotalM2Shipped]*[PickupDeliveryFee],2) AS
PickupDeliveryFeeDisb, tblinventory.PriceM2Shipped,
tblinventory.TotalM2Shipped, tblinventory.WeightM2,
tblinventory.PricePerSqFoot, tblinventory.strikeofffee,
tblinventory.shippingmethod, tblinventory.shippingrate,
Round([sqfeet]*[shippingrate],2) AS ShippingCost,
tblinventory.actualshippingcost,
Round([BWICargoFeeDisb]+[CustomsEntryFeesDisb]+[CustomsEntryBondFeeDisb]+[CustomsEntryTaxesDisb]+[PickupDeliveryFeeDisb],2)
AS TotalImportingFees, Round([ttlm2shipped]*[PriceM2Shipped],2) AS
TotalRugProductionCostPreDisc, tblinventory.NepalDiscountPercent,
[TotalRugProductionCostPreDisc]*[NepalDiscountPercent] AS DiscAmt,
Round([TotalRugProductionCostPreDisc]-[TotalRugProductionCostPreDisc]*[NepalDiscountPercent],2)
AS TotalRugProductionCost,
Round([TotalRugProductionCost]+[ttlshippingcostfromNepal],2) AS
TotalRugCostProdShipping,
Round([TotalRugCostProdShipping]+[TotalImportingFees],2) AS
TotalRugCostProdUSEntry,
Round([TotalImportingFees]+[TtlshippingcostfromNepal],2) AS
TotalShippingImportFees, Round([SqFeet]*[Pricepersqfoot]+[strikeofffee],2) AS
totalprice, Round([OrderM2]*[PriceM2Shipped],2) AS totalM2Price,
[ttlm2shipped]*[PriceM2Shipped] AS totalM2PriceReceived, "<center>" &
[DesignName] & "<BR>" & [DesignNumber] & "<BR>" & [SizeFeet] & "<BR>" &
[Colours] & "<BR>" & [Quality] & " Knots" & "<BR>" & [Contents] & "<BR>" &
[Notes/Comments] & "</center>" AS comment,
([TotalPrice]-[TotalRugCostProdUSEntry]) AS TotalProfit,
tblinventory.PrintLabel, tblinventory.PrintOrder,
tblinventory.NepaltoKarmaRugStatus, tblinventory.NomadWeaversInvoiceDate,
tblinventory.NomadWeaversInvoiceM2, tblinventory.[NomadWeaversInvoice#],
([PriceM2Shipped]*[NomadWeaversInvoiceM2]) AS NepalInvoicePrice
FROM tblinventory;
Click to expand...
Click to expand...
Click to expand...
 
Top