Division by Zero?

[casey]

I'm trying to trouble-shoot an undocumented datbase built by someone else and running into a recurring problem. She has it set up where a macro runs several queries. When these macros are run, the queries cause "Division by Zero" errors which stops the macro in its tracks. The following is a query that causes this error, but by looking at this query, I see no place where a division takes place. I'm assumming that another porblem is occuring and Access just states that it's division by zero error when it's actually something else.

1) Has anyone seen a "Division by Zero" error when running a query when it's actually something else causing the problem?
2) Can anyone see from the query what could possibly cause this and give me a hint as to a good way to trouble-shoot these queries to possibly handle whatever problem is occurring.

Thanks,

The query is as follows:
SELECT [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales AS [MTD NDS], [004c YTD Cogs by Category qry].SumOfNetSales AS [YTD NSD], [Buyer Category Fiscal Period for Report].EndDate
FROM ((((((((((((BuyerCategory LEFT JOIN dbo_Category ON BuyerCategory.CategoryID = dbo_Category.CategoryID) LEFT JOIN [008c GMROI by Category] ON BuyerCategory.CategoryID = [008c GMROI by Category].CategoryID) LEFT JOIN [016c MTD GM by Category] ON BuyerCategory.CategoryID = [016c MTD GM by Category].CategoryID) LEFT JOIN [017c YTD GM by Category] ON BuyerCategory.CategoryID = [017c YTD GM by Category].CategoryID) LEFT JOIN [010c Turns by Category] ON BuyerCategory.CategoryID = [010c Turns by Category].CategoryID) LEFT JOIN [005c MTD Cogs by Category qry] ON BuyerCategory.CategoryID = [005c MTD Cogs by Category qry].CategoryID) LEFT JOIN [004c YTD Cogs by Category qry] ON BuyerCategory.CategoryID = [004c YTD Cogs by Category qry].CategoryID) LEFT JOIN [006c MonthEnd Inv by Category qry] ON BuyerCategory.CategoryID = [006c MonthEnd Inv by Category qry].CategoryID) LEFT JOIN [018c MTD GPM by Category] ON BuyerCategory.CategoryID = [018c MTD GPM by Category].CategoryID) LEFT JOIN [019c YTD GPM by Category] ON BuyerCategory.CategoryID = [019c YTD GPM by Category].CategoryID) LEFT JOIN [015c Turns Buyer Category] ON BuyerCategory.Buyer = [015c Turns Buyer Category].Buyer) LEFT JOIN [014c GMROI Buyer Category] ON BuyerCategory.Buyer = [014c GMROI Buyer Category].Buyer) LEFT JOIN [Buyer Category Fiscal Period for Report] ON (BuyerCategory.Buyer = [Buyer Category Fiscal Period for Report].Buyer) AND (BuyerCategory.CategoryID = [Buyer Category Fiscal Period for Report].CategoryID)
GROUP BY [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales, [004c YTD Cogs by Category qry].SumOfNetSales, [Buyer Category Fiscal Period for Report].EndDate
ORDER BY BuyerCategory.Buyer;

 

[casey]

Good point, Pat.

I've done that and have found some division calculations. Would you suggest putting a iif() function to test for a value = 0 on the denominator? Or is there a better way to do this?

Thanks,

 

[casey]

Thanks, Pat! That seemed to work.

I have another problem now. It says that there's a type mismatch between the criteria. I think it's with the CategoryID; some are text and others are numbers. Is there something I can do to make these match. Possibly encapsulate them with the Str() or Val() function. Any ideas?

BuyerCategory.CategoryID = [016c MTD GM by Category].CategoryID) LEFT JOIN [017c YTD GM by Category] ON BuyerCategory.CategoryID = [017c YTD GM by Category].CategoryID) LEFT JOIN [010c Turns by Category] ON BuyerCategory.CategoryID = [010c Turns by Category].CategoryID) LEFT JOIN [005c MTD Cogs by Category qry] ON BuyerCategory.CategoryID = [005c MTD Cogs by Category qry].CategoryID) LEFT JOIN [004c YTD Cogs by Category qry] ON BuyerCategory.CategoryID = [004c YTD Cogs by Category qry].CategoryID) LEFT JOIN [006c MonthEnd Inv by Category qry] ON BuyerCategory.CategoryID = [006c MonthEnd Inv by Category qry].CategoryID) LEFT JOIN [018c MTD GPM by Category] ON

 

[casey]

GROUP BY clause

It is very wierd that if I remove the GROUP BY clause, the query runs without the "type-mismatch" error. When I re-enter the GROUP BY clause, it gives me the error again. Can anyone give me a clue as to why this would happen?

GROUP BY [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales, [004c YTD Cogs by Category qry].SumOfNetSales, [Buyer Category Fiscal Period for Report].EndDate

Thanks,