Getting an error in Access query Division by Zero

Question

I am trying to divide one field by another in a query.

There are no empty or blank or zeros in the fields I am using.  There are some negative numbers.

The numerator is a dollar amount which can be a negative number

The denominator is a general number and none are zeros.

How can I fix?

Answer 1

How do you achieve a negative cost/mile?

Maybe use ---

   Sum([RO Total Cost])/Sum([TotalMiles]) AS Expr1

Answer 2

No.  It is Currency.  Here is the design view of the table.

Answer 3

Here is the SQL

SELECT [Trailer Repair Order Detail Report].Trailer, [Trailer Repair Order Detail Report].Mth, [Trailer Repair Order Detail Report].Yr, Sum([Order Data].TotalMiles) AS SumOfTotalMiles, Max([Trailer Repair Order Detail Report].[RO Total Cost]) AS [MaxOfRO Total Cost], Sum([RO Total Cost]/[TotalMiles]) AS Expr1

FROM [Trailer Repair Order Detail Report] INNER JOIN [Order Data] ON [Trailer Repair Order Detail Report].Trailer = [Order Data].Trailer

GROUP BY [Trailer Repair Order Detail Report].Trailer, [Trailer Repair Order Detail Report].Mth, [Trailer Repair Order Detail Report].Yr

HAVING ((([Trailer Repair Order Detail Report].Mth)="May") AND (([Trailer Repair Order Detail Report].Yr)=2014));

Here is some sample data.  There are no blanks or zeros in either column.

12001	May	2014	1917	$926.05
12002	May	2014	2870	$628.03
12003	May	2014	3145	$855.10
12004	May	2014	4313	$1.98
12005	May	2014	4696	($37.77)
13001	May	2014	6272	$916.66

Answer 4

By correcting the likely error in your query or in your data... which we cannot see. Please post the SQL view of the query and if possible some sample data that triggers the error.

Answer 5

Hi,

please post we your Sql code.

Mimmo