Results 1 to 3 of 3
  1. #1
    VariableZ's Avatar
    VariableZ is offline Partially Knowledgeable
    Windows Vista Access 2007
    Join Date
    May 2011
    Location
    Georgia, USA
    Posts
    40

    Sum giving "Data type mismatch in criteria expression." error

    I'm pulling data from an SQL database into Access2007 through a linked table. There are two time fields: [STOP] and [ACK], both in the form HH:MM:SS, and I have a field called DOWNTIME_SEC that calculates the difference between those times, in seconds, with the formula



    CLng(IIf([STOP]="ERR" Or [ACK]="ERR","",Round((CDate([ACK])-CDate([STOP]))*60*60*24,0)))

    which gives me accurate results.

    My report is grouped by date, and the date footer contains the formula =Sum([DOWNTIME_SEC]), but that causes the error "Data type mismatch in criteria expression."

    I've tried CDbl, Val, etc, but to no avail. CInt gives me an overflow error. I've even created another field called [OTHER] and tried converting [DOWNTIME_SEC] to various formats, but I end up with the same problem. I've created another table and typed the data in manually (using the same formula for [DOWNTIME_SEC]), produced a report from it, and THAT works, but I can't do that hundreds of times a day every day. I would like to do this with only regular queries and without resorting to VBA, Macros, or any other 'fancy' stuff. Any suggestions?

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    off the cuff thoughts....

    CLng(IIf([STOP]="ERR" Or [ACK]="ERR","",....etc

    might change to:
    CLng(IIf([STOP]="ERR" Or [ACK]="ERR",0,....etc

    and I am just dwelling on casting a date nested inside a cast for long and wondering about that....
    Round((CDate([ACK])-CDate([STOP]))*60*60*24,0)))

    sanity check maybe splitting those two steps and eyeballing things....maybe test each half separately

    maybe helps - not sure....

  3. #3
    VariableZ's Avatar
    VariableZ is offline Partially Knowledgeable
    Windows Vista Access 2007
    Join Date
    May 2011
    Location
    Georgia, USA
    Posts
    40
    Ah yes, it's always the little things, isn't it? Changing "" to 0 did the trick. I'm glad I spent 12 hours with my head up my rump. Thanks for the help!

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

Similar Threads

  1. Data type mismatch in criteria expression
    By Douglasrac in forum Forms
    Replies: 3
    Last Post: 11-23-2010, 10:46 AM
  2. data type mismatch in criteria expression
    By broecher in forum Access
    Replies: 4
    Last Post: 09-10-2010, 08:29 AM
  3. Data type mismatch in criteria expression
    By shexe in forum Queries
    Replies: 2
    Last Post: 09-01-2010, 12:47 PM
  4. aSTR = Dir("C:\*.*") >> "Type Mismatch"
    By JGrant in forum Programming
    Replies: 1
    Last Post: 03-28-2009, 05:17 AM
  5. "Type Mismatch" Error
    By elmousa68 in forum Access
    Replies: 2
    Last Post: 12-05-2006, 08:28 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