Criteria Error that makes no sense

[RayMilhon]

I have a report in MS Access 2010. In the Group Footer Part of the calculations are from a different record source than the Main Report. I've built VBA Code that runs a query and returns the records that populate the 4 fields on the report. It's worked fine until now. Now everytime I run the report I get a Datatype Mismatch in criteria expression. The confusing part is it doesn't occur until the 4th page of the report even though the only thing that changes is the Criteria which consists of 2 fields firstname and lastname. Copying the sql into query analyzer gives me the same error. However, if I pull out the criteria and run the report the data provided matches what I have If I do a copy and paste of either into the criteria of the query I get the same error. Here's the query

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]) = "ALTAYS" AND FnfirstName([Providers]) = "MIKE";


'Functions

Public Function fnlastname(ByVal strname As String) As String
    Dim tmpstr As String
    tmpstr = Left(strname, InStr(1, strname, ",") - 1)
    If Right(tmpstr, 4) = "M.D." Then
        tmpstr = Left(tmpstr, Len(tmpstr) - 4)
    End If
    fnlastname = tmpstr
    
End Function

Public Function fnfirstname(ByVal strname As String) As String
    Dim tmpstr As String, lngfirstcomma As Long, lngsecondcomma As Long

    tmpstr = Right(strname, Len(strname) - InStr(1, strname, ","))
    
    Select Case Left(tmpstr, 4)
        Case Is = " DO ", Is = " MD "
            tmpstr = Right(tmpstr, Len(tmpstr) - 4)
        Case Is = " M.D", Is = " MD,", Is = " DO,", Is = " D.O", Is = "MD (", Is = "(PCP"
            lngfirstcomma = InStr(1, tmpstr, ",")
            lngsecondcomma = InStr(lngfirstcomma, tmpstr, ",")
            tmpstr = Right(tmpstr, Len(tmpstr) - lngsecondcomma)
    End Select

    tmpstr = Trim(tmpstr)
    If Left(tmpstr, 5) = "(PCP)" Then
        tmpstr = Right(tmpstr, Len(tmpstr) - 6)
    End If
    If InStr(1, tmpstr, " ") > 0 Then
        fnfirstname = Left(tmpstr, InStr(1, tmpstr, " ") - 1)
    Else
        fnfirstname = tmpstr
    End If
    
End Function

[CJ_London]

First, I would check your data

this line of your code relies on there being a comma in strName

tmpstr = Left(strname, InStr(1, strname, ",") - 1)

if there isn't Instr will return 0 which means the Left function will return an error

[RayMilhon]

Ajax, the strname is always in the format "Lastname, Degree, firstname MI" All of them are Primary Care Physicians and will all have the name in that format.

However, with that said I did go back and look at the Data and discovered there were 3 blank records appended to the table. The data for this section of the report comes to me via Excel spreadsheet which I then import into MS Access. This is the first time there were extra records. I'll have to relook at the spreadsheet and determine what happened there. Deleted the 3 extra records and it works great. Thanks