Results 1 to 6 of 6
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Data Mismatch

    I have a query in Microsoft Access that ran successfully last month. query is below



    Code:
    SELECT HVVMG.Providers, [Doctor-Patient Communication] as DP_comm,[Office Staff] as off,
    [Continuity and Coordination of Care] as coord_of_care,HVVMG.[Overall Ratings - Provider] as Overall, HVVMG.[Timely Access to Care] As Access, 
    HVVMG.Respondents,HVVMG.Facilities as fac, HVVMG.all_ques_avg,HVVMG.all_ques_avg_rank,HVVMG.MTC_75,HVVMG.MTC_90 
    FROM HVVMG 
    Where fnLastname([Providers]) = "SMITH" AND FnfirstName([Providers]) = "JOHN";
    The Error I'm getting is Data type Mismatch in Criteria Expression.

    If fnLastname and fnfirstname return a string and the Criteria is "JOHN" or "SMITH" which is also a string how is that a data type mismatch?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Could be in the functions, particularly if it worked previously (ie it's data related, something in the current data screws up the function). It works without the criteria? What do the functions look like? Any Null values in the field, that type of thing?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    If I take out the criteria the query works and the function values that are returned match the criteria that was there. Also, the query with criteria normally returns only 1 row. The query normally is run in VBA in the group footer section of a report to populate 3 fields on the report. The code for that is below:

    Code:
        strsql = "SELECT HVVMG.Providers, [Doctor-Patient Communication] as DP_comm,[Office Staff] as off,[Continuity and Coordination of Care] as coord_of_care," & _
            "HVVMG.[Overall Ratings - Provider] as Overall, HVVMG.[Timely Access to Care] As Access, HVVMG.Respondents," & _
            "HVVMG.Facilities as fac, HVVMG.all_ques_avg,HVVMG.all_ques_avg_rank,HVVMG.MTC_75,HVVMG.MTC_90 " & _
            "FROM HVVMG " & _
            "Where fnLastname([Providers]) = """ & fnlastname(Me.PRIMARY_CARE_PHYSICIAN) & """ AND FnfirstName([Providers]) = """ & _
            fnfirstname(Me.PRIMARY_CARE_PHYSICIAN) & """;"
        rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        Do Until rs.EOF
        If Not rs.EOF Then
            txt_Respondents = rs!respondents
            txt_MD_Pat_Comm = rs!DP_Comm
            txtofficestaff = rs!off
            txt_Cont_Cord_of_Care = rs!coord_of_care
            txt_Survey_Access = rs!Access
            txt_Survey_Overall = rs!Overall
            txt_survey_fac = rs!fac
            txt_all_ques_avg = rs!all_ques_avg
            txt_all_ques_avg_rank = rs!all_ques_avg_rank
            txt_MTC_75th = rs!MTC_75
            txt_MTC_90th = rs!MTC_90
            
            rs.MoveNext
        End If
        Loop
        rs.Close
        Set rs = Nothing
    The error comes up on the rs.movenext line. If I remove the Do Loop and the rs.movenext the report works for the first 3 pages then fails on the 4th page. The HVVMG Table is an imported Excel spreadsheet. the spreadsheet is imported to the DB monthly and then the report is run. The spreadsheet comes from another company and I thought the spreadsheet was doing something with the formatting so when I did the import I made sure each of the fields was on the correct type. Didn't help.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The loop seems unnecessary, as you'll just end up with the last record's values, right? When it fails without the loop, where does it fail? What values are being passed to the functions at that point?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Don't understand it but I was able to fix it. Turned out that the main report had the Doctor's first name as Mike and the table I was querying had the same Doctor's first name as Mikel. Took of the l and the report worked fine. Just don't understand how that's a data mismatch error??? Oh well.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That is odd. I'd expect it to simply not return that record. Glad you got it fixed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Data mismatch
    By cbende2 in forum Access
    Replies: 13
    Last Post: 06-05-2015, 01:18 PM
  2. Data mismatch in Report
    By ragpatil in forum Programming
    Replies: 3
    Last Post: 09-07-2012, 12:43 PM
  3. Data Type Mismatch
    By timmy in forum Programming
    Replies: 9
    Last Post: 04-12-2011, 03:48 AM
  4. Data Mismatch?
    By Lockrin in forum Access
    Replies: 7
    Last Post: 06-14-2010, 03:17 PM
  5. Data Mismatch Error
    By sneupane in forum Access
    Replies: 5
    Last Post: 03-15-2010, 08:17 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