Results 1 to 3 of 3
  1. #1
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    51

    Invalid Decimal for Data Type - ERROR

    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

    Click image for larger version. 

Name:	error.png 
Views:	7 
Size:	37.4 KB 
ID:	31444

    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])));

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    ive seen some finicky queries like that.
    I fixed it with:
    instead of joining tables , make a query of each, convert the joining field so they both are in the same format....
    for CatagoryID, convert it using CLNG(CategoryID) in that query, say: Q1
    then the report uses a query using Q1 joined to Q2.

    When I convert the fields, the query/report seems to accept it.
    without it, Ive seen a case where 1 <> 1. (!!??!!)

  3. #3
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    51

    Still crapping out on me....... :(

    Quote Originally Posted by ranman256 View Post
    ive seen some finicky queries like that.
    I fixed it with:
    instead of joining tables , make a query of each, convert the joining field so they both are in the same format....
    for CatagoryID, convert it using CLNG(CategoryID) in that query, say: Q1
    then the report uses a query using Q1 joined to Q2.

    When I convert the fields, the query/report seems to accept it.
    without it, Ive seen a case where 1 <> 1. (!!??!!)


    Craziest thing I've ever seen with Access.... I can't understand why it's acting this way. I tried your suggestion but no dice. I can display each of the category and class fields in the report without a problem. The second I add even one as a group header - i get the error.

    Could it be because some of the fields are blank or null or whatever you'd call them? Do not contain any info? Could that trigger this? I'd rather not populate with a zero (0) but if you think that might solve the problem, I'll go down that road...

    Otherwise, I'm stumped.........

    Thx for helping though.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-01-2017, 11:00 PM
  2. Data type Error
    By Glenn_Suggs in forum Access
    Replies: 3
    Last Post: 01-25-2016, 11:30 AM
  3. FindFirst Not Working with Decimal Data Type
    By JonMulder in forum Programming
    Replies: 5
    Last Post: 01-22-2013, 01:26 PM
  4. Replies: 5
    Last Post: 01-14-2013, 03:04 PM
  5. Replies: 1
    Last Post: 05-11-2012, 10:59 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums