This makes NO sense...
I have a report with multiple layers of queries. I can run the main query that's attached to the report without an issue.
When I add a report Group using CategoryID to the report, I get an Invalid Decimal for Data Type error.
The CategoryID field in the ItemMaster is bound to tlkpProductCategory table. There are two columns. CategoryID (Long Integer/0 decimals) and Category (short text/50 char's.)
I tried joining tlkpProductCategory to the ItemMaster table in the report query and using the actual text Category field as opposed to the CategoryID numeric field but I get error regardless.
If I try to group on any of the newly added category or class fields, I get the error.
tlkpProductCategory
CategoryID
Category
tlkpProductSubCategory
SubCategoryID
SubCategory
CategoryID
tlkpProductClass
ClassID
Class
SubCategoryID
tlkpProductSubClass
SubClassID
SubClass
ClassID
All of the ID's are "Integer" w/o Decimals. Decimal = 0
All of the text fields are "Short Text" limited to 50 char's.
SubCategory table joined to Category table
Class table joined to SubCategory table
Sub Class table joined to Class table
If I run the report without any grouping - I don't get any errors.
The second I add any of these category or class fields as a group option in the report, I get the Invalid Decimal error even through there are NO decimals in any of these tables...
I could really use some help on this one!
Thanks!
Jason
Main Report Query
SELECT tblItemMaster.CATEGORYID, tblItemMaster.SUBCATEGORYID, tblItemMaster.CLASSID, tblItemMaster.SUBCLASSID, tblItemMaster.ITEMNUM, tblItemMaster.PRODUCTNAME, qryAverageQuantityAndCost2.AvgOfUnitsReceived, qryAverageQuantityAndCost2.SumOfUnitsReceived, qryAverageQuantityAndCost2.AvgOfITEMCOST, qryAverageQuantityAndCost2.SumOfTotalCost, Avg(qryAverageInvoiceItems_SubOrderDetails2.Invoic eItemQuantity) AS AvgOfInvoiceItemQuantity, Sum(qryAverageInvoiceItems_SubOrderDetails2.Invoic eItemQuantity) AS SumOfInvoiceItemQuantity, Avg(qryAverageInvoiceItems_SubOrderDetails2.Invoic eItemUnitPrice) AS AvgOfInvoiceItemUnitPrice, Sum(qryAverageInvoiceItems_SubOrderDetails2.Total) AS SumOfTotal, tlkpProductCategory.CATEGORY, tlkpProductSubCategory.SUBCATEGORY, tlkpProductClass.CLASS, tlkpProductSubClass.SUBCLASS
FROM (((((tblItemMaster LEFT JOIN qryAverageQuantityAndCost2 ON tblItemMaster.ITEMNUM = qryAverageQuantityAndCost2.ITEMNUM) LEFT JOIN qryAverageInvoiceItems_SubOrderDetails2 ON tblItemMaster.ITEMNUM = qryAverageInvoiceItems_SubOrderDetails2.InvoiceIte mDescription) LEFT JOIN tlkpProductCategory ON tblItemMaster.CATEGORYID = tlkpProductCategory.CATEGORYID) LEFT JOIN tlkpProductClass ON tblItemMaster.CLASSID = tlkpProductClass.CLASSID) LEFT JOIN tlkpProductSubCategory ON tblItemMaster.SUBCATEGORYID = tlkpProductSubCategory.SUBCATEGORYID) LEFT JOIN tlkpProductSubClass ON tblItemMaster.SUBCLASSID = tlkpProductSubClass.SUBCLASSID
GROUP BY tblItemMaster.CATEGORYID, tblItemMaster.SUBCATEGORYID, tblItemMaster.CLASSID, tblItemMaster.SUBCLASSID, tblItemMaster.ITEMNUM, tblItemMaster.PRODUCTNAME, qryAverageQuantityAndCost2.AvgOfUnitsReceived, qryAverageQuantityAndCost2.SumOfUnitsReceived, qryAverageQuantityAndCost2.AvgOfITEMCOST, qryAverageQuantityAndCost2.SumOfTotalCost, tlkpProductCategory.CATEGORY, tlkpProductSubCategory.SUBCATEGORY, tlkpProductClass.CLASS, tlkpProductSubClass.SUBCLASS
HAVING (((tblItemMaster.CATEGORYID)>0 And (tblItemMaster.CATEGORYID) Like Nz([Forms]![frmMain]![NavigationSubform]![Reports]![DisplayPanel].[Form].[txtCategoryID],"*")) AND ((tblItemMaster.SUBCATEGORYID) Is Null Or (tblItemMaster.SUBCATEGORYID) Like Nz([Forms]![frmMain]![NavigationSubform]![Reports]![DisplayPanel].[Form].[txtSubCategoryID],"*")) AND ((tblItemMaster.CLASSID) Is Null Or (tblItemMaster.CLASSID) Like Nz([Forms]![frmMain]![NavigationSubform]![Reports]![DisplayPanel].[Form].[txtClassID],"*")) AND ((tblItemMaster.SUBCLASSID) Is Null Or (tblItemMaster.SUBCLASSID) Like Nz([Forms]![frmMain]![NavigationSubform]![Reports]![DisplayPanel].[Form].[txtSubClassID],"*")))
ORDER BY tblItemMaster.CATEGORYID, tblItemMaster.SUBCATEGORYID, tblItemMaster.CLASSID, tblItemMaster.SUBCLASSID, tblItemMaster.PRODUCTNAME
This query and the one below joined to Main
SELECT qryAverageQuantityAndCost.ITEMNUM, Avg(qryAverageQuantityAndCost.UnitsReceived) AS AvgOfUnitsReceived, Sum(qryAverageQuantityAndCost.UnitsReceived) AS SumOfUnitsReceived, Avg(qryAverageQuantityAndCost.ITEMCOST) AS AvgOfITEMCOST, Sum(qryAverageQuantityAndCost.TotalCost) AS SumOfTotalCost
FROM qryAverageQuantityAndCost
GROUP BY qryAverageQuantityAndCost.ITEMNUM;
SELECT tblItemMaster.ITEMNUM, tblItemMaster.PRODUCTNAME, tblCosting.PURDATE, tblCosting.UnitsReceived, tblCosting.ITEMCOST, Nz([unitsreceived])*Nz([itemcost]) AS TotalCost
FROM tblItemMaster LEFT JOIN tblCosting ON tblItemMaster.ITEMNUM = tblCosting.ITEMNUM
GROUP BY tblItemMaster.ITEMNUM, tblItemMaster.PRODUCTNAME, tblCosting.PURDATE, tblCosting.UnitsReceived, tblCosting.ITEMCOST, Nz([unitsreceived])*Nz([itemcost])
HAVING (((tblCosting.PURDATE) Between ([Forms]![frmMain]![NavigationSubform]![Reports]![DisplayPanel].[Form].[InvoiceHeaderDateFrom]) And ([Forms]![frmMain]![NavigationSubform]![Reports]![DisplayPanel].[Form].[InvoiceHeaderDateTo])));
This one and the one below joined to the main
SELECT qryAverageInvoiceItems_SubOrderDetails.InvoiceItem Description, qryAverageInvoiceItems_SubOrderDetails.InvoiceItem Quantity, qryAverageInvoiceItems_SubOrderDetails.InvoiceItem UnitOrCase, qryAverageInvoiceItems_SubOrderDetails.InvoiceItem UnitPrice, qryAverageInvoiceItems_SubOrderDetails.Total
FROM qryAverageInvoiceItems_SubOrderDetails;
SELECT tblSalesInvoiceHeaders.InvoiceHeaderShipDate, tblSalesInvoiceItems.InvoiceItemDescription, tblSalesInvoiceItems.InvoiceItemQuantity, tblSalesInvoiceItems.InvoiceItemUnitOrCase, tblSalesInvoiceItems.InvoiceItemUnitPrice, Nz([invoiceitemquantity])*Nz([invoiceitemunitprice]) AS Total
FROM tblSalesInvoiceHeaders INNER JOIN tblSalesInvoiceItems ON tblSalesInvoiceHeaders.InvoiceHeaderNum = tblSalesInvoiceItems.InvoiceItemHeader
WHERE (((tblSalesInvoiceHeaders.InvoiceHeaderShipDate) Between ([Forms]![frmMain]![NavigationSubform]![Reports]![DisplayPanel].[Form].[InvoiceHeaderDateFrom]) And ([Forms]![frmMain]![NavigationSubform]![Reports]![DisplayPanel].[Form].[InvoiceHeaderDateTo])));