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.