#Error

[Guest]

I am working on a perodicals database that holds usage statistics. I am
using the following code to calculate the cost per use, by dividing the cost
by the use. However if the use value is 0, the cost per use is displayed as
#Error. How can I rid of this error message and just have a zero value
displayed.

See below the SQL statements used to code this query:

SELECT Listings.Title, Listings.Cost, Listings.Use, [cost]/[use] AS [Cost
per Use], Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;

Any help with this problem is grately appreciated.

Sincerely,

Tiffany Miller

 

[John Spencer (MVP)]

Use an IIF function in the query. I might also use the NZ (Null to Zero)
function to force a zero value for the null.

SELECT Listings.Title, Listings.Cost, Listings.Use,
IIF(Nz([Use],0)>0,[cost]/[use],0) AS [Cost per Use],
Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;