#Div/0! error

[rowfei]

I have a query with code below:

SELECT [Daily Sale Details Extended].[Sold Date], [Daily Sale Details Extended].[Selling Tool] AS Tool, Products.[Product Name] AS Product, [Daily Sale Details Extended].Quantity AS QTY, [Daily Sale Details Extended].[Extend Price] AS Price, [Daily Sale Details Extended].[Shipping Charge] AS Shipping, [Daily Sale Details Extended].Profit, [Daily Sale Details Extended].Percentage, [Daily Sale Details Extended].[Item#/Order ID], [Daily Sale Details Extended].Cost, [Daily Sale Details Extended].[Total cost]
FROM [Daily Sale Details Extended] INNER JOIN Products ON [Daily Sale Details Extended].[Product ID] = Products.ID
WHERE ((([Daily Sale Details Extended].Sold)=Yes Or ([Daily Sale Details Extended].Sold)=Yes))
ORDER BY [Daily Sale Details Extended].[Sold Date] DESC , [Daily Sale Details Extended].[Selling Tool], Products.[Product Name];

If the profit is negative, the Percentage will display #Div/0!. How can make the percentage field display 0 instead of #Div/0!?

Thanks in advance.

 

[MSAccessRookie]

I have a query with code below:

SELECT [Daily Sale Details Extended].[Sold Date], [Daily Sale Details Extended].[Selling Tool] AS Tool, Products.[Product Name] AS Product, [Daily Sale Details Extended].Quantity AS QTY, [Daily Sale Details Extended].[Extend Price] AS Price, [Daily Sale Details Extended].[Shipping Charge] AS Shipping, [Daily Sale Details Extended].Profit, [Daily Sale Details Extended].Percentage, [Daily Sale Details Extended].[Item#/Order ID], [Daily Sale Details Extended].Cost, [Daily Sale Details Extended].[Total cost]
FROM [Daily Sale Details Extended] INNER JOIN Products ON [Daily Sale Details Extended].[Product ID] = Products.ID
WHERE ((([Daily Sale Details Extended].Sold)=Yes Or ([Daily Sale Details Extended].Sold)=Yes))
ORDER BY [Daily Sale Details Extended].[Sold Date] DESC , [Daily Sale Details Extended].[Selling Tool], Products.[Product Name];

If the profit is negative, the Percentage will display #Div/0!. How can make the percentage field display 0 instead of #Div/0!?

Thanks in advance.

I do not see a division by zero occurring in this Query. If [Daily Sale Details Extended] is another Query, then the error could be happening there. If you cannot find the error, show us the second Query (and any others if there happen to be more).

As a general rule, you should be able to use an IIf() to prevent a division by zero error, and use a value of zero instead by doing something like the following:

IIf(B=0, 0, A/B)

Of course, you need so make this right for the code.

 

[Kiwiman]

Howzit

You will get this in a division formula if the denominator is 0 (as you cannot divide by zero) not negatve. So you want something like:

iif(yourdenominator=0,0,yournumerator/yourdenominator) AS Proit_PC

I'm not sure where abouts in your SQL you have this error.

 

[plog]

There's no division going on in that query. I think you need to back up and fix it in the underlying query. Post that SQL.

 

[ulieq]

You may have null values in there. Set a default value to a number or 0 for a simple solution.