Results 1 to 7 of 7
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Data Type Mismatch when Trying to Sum a Qry

    I swear I did the exact same thing yesterday and had no problems and this morning I am having nothing but trouble.

    Trying to create a simple qry that sums transactions by Month,Category,Amount

    So for example

    Jan Bills $1200
    Jan Rent $2700


    Jan Lawn Care $480


    when i turn it into a sumqry it gives me a data type mismatch in criteria expression error regardless if I try to sum the amounts column or not



    Please tell me what I am doing wrong or where an error might be

    Thanks

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Without any information about the base tables nor the query SQL it's hard to help.

  3. #3
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    tbl_transactions : TransID, TransDate, CategoryName,TransAmount

    tbl_Category: CategroyID, CategoryName


    in the first type of qry useing month numbers I have no problems (below I am listing down what would be across in the qry design view

    Formula from builder --> TransMonthNum: Month([TransDate])

    CategoryName from tbl_Category

    TransAmount from tbl_Transactions

    again this setup has no problems it returns the month number and will let me, group by month number, group by category name, sum of TransAmmount


    HOWEVER

    Formula from builder --> TransMonthName: MonthName(Month([TransDate]))

    CategoryName from tbl_Category

    TransAmount from tbl_Transactions

    This setup works for returning the Month Name of the transaction date, but as soon as I try to make it a SUMqry and group by anything it gives me the Data type mismatch in criteria expression error


    I'm not sure if it will work or not but I'm getting ready to turn my laptop into a Frisbee and throw it over the fence, it may not solve the problem but it will at least prevent me from working on a solution!

    Thanks for taking the time.

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    First of all, it is a bit strange that you have a separate table for categories, but use the categoryName in table transactions. I would say:

    tbl_transactions : TransID, TransDate, CategoryID,TransAmount
    tbl_Category: CategoryID, CategoryName

    This way you link the two tables on the CategoryID, probably an autonumber in tbl_Category, so a number filed - long integer in tbl_transactions.

    Than: if you do the sum by month, preceed it with a group by year. You don't want to make the sum of Jan 2019 + Jan 2020 + ...

    The following query should work after changing the table links:

    Code:
    
    
    Code:
    SELECT tbl_Category.CategoryName, Year([transDate]) AS YearTrans, Month([transDate]) AS MonthTrans, Sum(tbl_Transaction.TransAmount) AS SumOfTransAmount
    FROM tbl_Category INNER JOIN tbl_Transaction ON tbl_Category.CategoryID = tbl_Transaction.CategoryID
    GROUP BY tbl_Category.CategoryName, Year([transDate]), Month([transDate]);


  5. #5
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    NO NO! Sorry, yes my tables are set up as you said, Just wasn't thinking when I typed it out in the post.

    And yes I agree the long term solution would be to group by year as well. this db was only started 3 months ago so it still only has 2020 transactions,

    I was just trying to get a simple qry that would show me how much money I spent in categories by month, trying to learn this small chunk of "group group sum" before trying to understand something as complex as the SQL statement posted above.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    the above SQL statement is generated in the design view of the standard query by example screen. It's a simple groupsquery :-)

    Click image for larger version. 

Name:	simplegroupsqry.JPG 
Views:	13 
Size:	38.6 KB 
ID:	42357

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi James, I wonder if there are any records in the transactions table that are missing the TransDate, as that would trigger the error message you are getting as you cannot get the MonthName(Month(NULL)).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Data Type MisMatch
    By Eranka in forum Access
    Replies: 4
    Last Post: 10-31-2019, 03:58 AM
  2. Data Type MisMatch
    By Eranka in forum Access
    Replies: 2
    Last Post: 10-23-2019, 04:43 AM
  3. Data type mismatch
    By DMT Dave in forum Access
    Replies: 7
    Last Post: 06-14-2019, 10:00 AM
  4. Data Type Mismatch
    By Mtyetti2 in forum Queries
    Replies: 3
    Last Post: 10-23-2013, 11:48 AM
  5. Data Type Mismatch
    By timmy in forum Programming
    Replies: 9
    Last Post: 04-12-2011, 03:48 AM

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