locked
Bad syntax in VBA sql query RRS feed

  • Question

  • Getting a type mismatch error for this code:

    strSQL = "SELECT R.[Claim Number], R.RptGuideComments, R.CriticalThnkCOmments, " & _
                    "R.AssessmentComments, R.DiscPlanComments, R.PerfBudgetComments, R.OutComeComments, " & _
                    "R.UseAgainComments, R.AddlComments " & _
                    "FROM ReportCards R " & _
                    "WHERE  R.RptGuideComments LIKE ""*" & wordArray(intI) & "" * " OR R.CriticalThnkCOmments LIKE ""*" & wordArray(intI) & "*"" OR R.AssessmentComments LIKE ""*" & wordArray(intI) & "*"" OR R.DiscPlanComments ""*" & wordArray(intI) & "*"" OR R.PerfBudgetComments ""*" & wordArray(intI) & "*"" OR R.OutComeComments ""*" & wordArray(intI) & "*"" OR R.UseAgainComments  ""*" & wordArray(intI) & "*"" OR R.AddlComments  ""*" & wordArray(intI) & "*"" ;"
        

    Anyone got thoughts on why?

    Thursday, May 14, 2015 9:20 PM

Answers

  • The quotes before the first OR are misplaced, and you missed several instances of LIKE. This should work better:

        strSQL = "SELECT R.[Claim Number], R.RptGuideComments, R.CriticalThnkCOmments, " & _
            "R.AssessmentComments, R.DiscPlanComments, R.PerfBudgetComments, " & _
            "R.OutComeComments, R.UseAgainComments, R.AddlComments " & _
            "FROM ReportCards R WHERE " & _
            "R.RptGuideComments LIKE ""*" & wordArray(intI) & "*"" OR " & _
            "R.CriticalThnkCOmments LIKE ""*" & wordArray(intI) & "*"" OR " & _
            "R.AssessmentComments LIKE ""*" & wordArray(intI) & "*"" OR " & _
            "R.DiscPlanComments LIKE ""*" & wordArray(intI) & "*"" OR " & _
            "R.PerfBudgetComments LIKE ""*" & wordArray(intI) & "*"" OR " & _
            "R.OutComeComments LIKE ""*" & wordArray(intI) & "*"" OR " & _
            "R.UseAgainComments LIKE ""*" & wordArray(intI) & "*"" OR " & _
            "R.AddlComments LIKE ""*" & wordArray(intI) & "*"" ;"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, May 14, 2015 10:21 PM