Results 1 to 12 of 12
  1. #1
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17

    Question Data type mismatch in criteria expression for overdue action items

    Hey all,



    I am trying to created a query that pulls everything from my table but only shows the ones that are overdue. In the criteria of the due date column I have <=Date(), but it is giving me the mismatch error. How can I build this query based on only over due items?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    I presume this is the same issue you are having here https://www.accessforums.net/showthread.php?t=67032

    your dteRegularStyle function is expecting a string - are you quire sure you never receive a null value for RECEIVED_DATE?

    if you do, you cannot pass a null as a string
    so try changing your function to

    dteRegularStyle(dteCLAIMS As Variant) As Date

    due to the lack of indenting, it is very difficult to read your code but also the function is supposed to return a date - but it looks like it is assigned a string

    dteRegularStyle = dteRegularMonth & "/" & dteRegularDay & "/" & dteRegularYear

    I would expect this to be

    dteRegularStyle = DateValue(dteRegularMonth & "/" & dteRegularDay & "/" & dteRegularYear)

  3. #3
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17
    I apologize but I am really confused by this answer... I didnt post any code in my original post and I dont have anything name received_date. I am just trying to get it to take the date the use enters (which says it is a date/time data type) and in the query only pull the items that are currently over due.. For example if the due date says it was due 7/10/17 but today is 7/14/17 then this item should show up on the query... I am not a big coder and I am trying to achieve this through the query design page..

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Please show us the code involved. How is the due date defined?

  5. #5
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17
    Here is what is under the SQL code:

    Code:
    SELECT [Action Items].[Date Added], [Action Items].[Added By], [Action Items].Participants, [Action Items].[Origin of Issue], [Action Items].[Department Responsible], [Action Items].Line, [Action Items].Shift, [Action Items].Area, [Action Items].Equipment, [Action Items].Status, [Action Items].Priority, [Action Items].[Due Date], [Action Items].[Completion Date], [Action Items].[Assigned To], [Action Items].[Description of Issue/Problem], [Action Items].[Corrective Action], [Action Items].Comments, [Action Items].Field1, [Action Items].Field1.FileData, [Field1].[FileFlags] AS Expr1, [Action Items].Field1.FileName, [Field1].[FileTimeStamp] AS Expr2, [Action Items].Field1.FileType, [Field1].[FileURL] AS Expr3FROM [Action Items]
    WHERE ((([Action Items].[Due Date])<=Date()) AND (([Action Items].[Completion Date])<>"(4) Completed"));
    and it is a date/time data type. It looks like 7/12/2017

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Here is a formatted view of your SQL
    Code:
    SELECT [Action Items].[Date Added]
    	,[Action Items].[Added By]
    	,[Action Items].Participants
    	,[Action Items].[Origin of Issue]
    	,[Action Items].[Department Responsible]
    	,[Action Items].Line
    	,[Action Items].Shift
    	,[Action Items].Area
    	,[Action Items].Equipment
    	,[Action Items].STATUS
    	,[Action Items].Priority
    	,[Action Items].[Due Date]
    	,[Action Items].[Completion Date]
    	,[Action Items].[Assigned To]
    	,[Action Items].[Description of Issue/Problem]
    	,[Action Items].[Corrective Action]
    	,[Action Items].Comments
    	,[Action Items].Field1
    	,[Action Items].Field1.FileData
    	,[Field1].[FileFlags] AS Expr1
    	,[Action Items].Field1.FileName
    	,[Field1].[FileTimeStamp] AS Expr2
    	,[Action Items].Field1.FileType
    	,[Field1].[FileURL] AS Expr3FROM [Action Items]
    WHERE (
    		(([Action Items].[Due Date]) <= DATE ())
    		AND (([Action Items].[Completion Date]) <> "(4) Completed")
    		);

    This part looks suspicious to me
    (([Action Items].[Completion Date]) <> "(4) Completed")

  7. #7
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17
    Thank you that part is not what is giving me the error. Its the date.. The last line is just saying that if an items status says completed to not count it as overdue

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Can you post the design for table [Action Items]? To show readers how the fields are defined/dimmed.

    If [Completion Date] has datatype Date/Time, then comparing it to a string seems an error.

    Also, try a space between 3 and F in this line of sql
    [Field1].[FileURL] AS Expr3FROM [Action Items]

    [Field1].[FileURL] AS Expr3 FROM [Action Items]


    What exactly is Field1?

    Good luck.

  9. #9
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17
    Field 1 is attachments. Is there anyway I can attach the file here?

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    I didnt post any code in my original post
    my bad, different OP but asking the same question - or at least having the same problem

  11. #11
    Dscalf1 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    17
    I figured it out yall thank you for all the help. Yall were correct in saying the problem was with the other criteria... I had it under the wrong column

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    It may be helpful to others if you could show us the corrected code. Lots of posters say they reviewed posts and found solutions.
    Glad you resolved the issue.

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

Similar Threads

  1. Data Type Mismatch in criteria expression
    By gaker10 in forum Queries
    Replies: 3
    Last Post: 03-05-2015, 01:54 PM
  2. Data type mismatch in criteria expression
    By khughes46 in forum Queries
    Replies: 12
    Last Post: 05-01-2014, 07:34 AM
  3. Data Type mismatch in criteria expression
    By elb1999 in forum Queries
    Replies: 2
    Last Post: 01-20-2012, 02:38 PM
  4. Data type mismatch in criteria expression
    By Douglasrac in forum Forms
    Replies: 3
    Last Post: 11-23-2010, 10:46 AM
  5. data type mismatch in criteria expression
    By broecher in forum Access
    Replies: 4
    Last Post: 09-10-2010, 08:29 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