Results 1 to 13 of 13
  1. #1
    SpdRacerX is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    40

    Data Mismatch in Criteria

    I created a query with expressions from a table. Now I'm trying to filter out the results of the expression using criteria in the query. I keep getting the dreaded "Data Type Mismatch in Criteria" error.

    My expressions in the query are:

    Over: IIf([Over Formula]>24,"Yes","No")
    Over Formula: (DateDiff("n",[Present],[Discharge]))/60
    Present: Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss")
    Discharge: Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss")

    I either want to have the "Yes" of the Over Expression to be returned only or the >24 of the Over Formula Expression to be returned only.

    The Present Date and Present Time and Discharge Date and Discharge Time Fields of the Table are all set at Date/Time in the table.

    I've tried multiple criteria's in the criteria expression with the same error message popping up every time.



    Are there too many expressions?

    Any help would be appreciated.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Could you please provide the SQL text of the query that had the criteria which returned the error?

  3. #3
    SpdRacerX is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    40
    SELECT [Cases Query2].SSN, [Cases Query2].Veteran, [Cases Query2].Present, [Cases Query2].Discharge, [Cases Query2].[Over Formula], [Cases Query2].Over
    FROM [Cases Query2]
    WHERE ((([Cases Query2].Over)="Yes"));

    This is where I get the "Data Type Mismatch in Criteria" Error Message

  4. #4
    SpdRacerX is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    40
    Even when I run the query "as-is" with no criterias and then try to filter the report from the query I get the "Data Type Mismatch" error message.

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    If you remove the criteria altogether will the query run? If not, you have an error in your select statement. If the query does not run when you add the criteria back in, you might use the actual expressions rather than the aliases in the WHERE clause

    WHERE ((((DateDiff("n",Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss"),Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss")))/60)>24));

    oops I see you added a new post

    How are you filtering the report?

  6. #6
    SpdRacerX is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    40
    It's funny because it seems to run the query, but then the error dialog box appears. I click on OK and then every field becomes #Name?

  7. #7
    SpdRacerX is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    40
    I opened the report which shows every record from the query (with no criteria). I then right click and filter.

  8. #8
    SpdRacerX is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    40
    Using the where clause you gave gives me the same error.

    WHERE ((((DateDiff("n",Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss"),Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss")))/60)>24));

  9. #9
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Using the where clause you gave gives me the same error.
    At what point, when you just run the query or when you run the report?


    I tried the criteria I gave you in a very simplified example database and it worked fine. If I use the aliases, the query asked for the values of the aliases as if they were parameters.

    I would focus on the query first. Once the query is running properly, then we can work on the report.

  10. #10
    SpdRacerX is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    40
    The query runs fine by itself with no criteria, it runs every field of every record. I added your "Where" clause to the SQL (took out the ; and then pasted it at the end). Then when I click to Run the query I get the error message right away.

    The SQL is now:

    SELECT Cases.*, Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss") AS Present, Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss") AS Discharge, Format([Eval Date] & " " & [Eval Time],"mm/dd/yyyy hh:nn:ss") AS Eval, Format([Dispo Date] & " " & [Dispo Time],"mm/dd/yyyy hh:nn:ss") AS Dispo, DateDiff("n",[Present],[Eval])\60 & ":" & Format(DateDiff("n",[Present],[Eval]) Mod 60,"00") AS [Present to Eval], DateDiff("n",[Dispo],[Discharge])\60 & ":" & Format(DateDiff("n",[Dispo],[Discharge]) Mod 60,"00") AS [Dispo to Discharge], DateDiff("n",[Present],[Discharge])\60 & Format(DateDiff("n",[Present],[Discharge]) Mod 60,"\:00") AS [Total Time in PER], IIf([Over Formula]>24,"Yes","No") AS Over, (DateDiff("n",[Present],[Discharge]))/60 AS [Over Formula], -Int(-(IIf([Team]="PER",[Over Formula]/24,1))) AS [PER Bed Days], -Int(-(IIf([Team]<>"PER",([Over Formula]/24)-1,0))) AS [Team Bed Days], (DateDiff("n",Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss"),Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss")))/60 AS Expr1
    FROM Cases
    WHERE ((((DateDiff("n",Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss"),Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss")))/60)>24));

  11. #11
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I'm not sure exactly what is going on but considering that Access stores dates and times as numbers not as mm/dd/yyyy hh:mm:ss, we may be able to just treat the date and time fields as numbers in the WHERE clause as follows:

    WHERE DateDiff("n",[Present Date]+[Present Time],[Discharge Date] + [Discharge Time])/60>24

    How do you define the date and time fields in your Cases table in terms of format? Present Date: short date perhaps?


    Another option is to force SQL to see the dates and time as just that using the DateValue() and TimeValue() functions

    WHERE DateDiff("n",DateValue([Present Date])+TimeValue([Present Time]),DateValue([Discharge Date]) + TimeValue([Discharge Time]))/60>24

    Out of curiosity, does this query run correctly:


    SELECT Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss") AS Present, Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss") AS Discharge
    FROM Cases
    WHERE ((((DateDiff("n",Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss"),Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss")))/60)>24));

    Or this one

    SELECT Format([Present Date] & " " & [Present Time],"mm/dd/yyyy hh:nn:ss") AS Present, Format([Discharge Date] & " " & [Discharge Time],"mm/dd/yyyy hh:nn:ss") AS Discharge
    FROM Cases

    WHERE DateDiff("n",DateValue([Present Date])+TimeValue([Present Time]),DateValue([Discharge Date]) + TimeValue([Discharge Time]))/60>24

  12. #12
    SpdRacerX is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    40
    Neither of those work. I did find some errors where there were negative results in the datediff expressions (said the patient came in before they left). I'm working on fixing those now in case that is the cause.

  13. #13
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Do you have any records where any of the date or time fields are null? That also might be causing a problem.

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

Similar Threads

  1. Data Type mismatch in criteria expression
    By elb1999 in forum Queries
    Replies: 2
    Last Post: 01-20-2012, 02:38 PM
  2. Data type mismatch in criteria expression
    By buienxg in forum Access
    Replies: 2
    Last Post: 11-22-2011, 10:29 AM
  3. Data type mismatch in query criteria
    By TinaCa in forum Queries
    Replies: 2
    Last Post: 09-19-2011, 11:31 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