Whats wrong with this Function

I keep receiving a Compile Error stating Expected: end of statement an it highlights "Active"

I know the strSQL works fine as a SQL statement in the query builder and I just can't figure out why it doesn't work here.  I think it has to do with the LIKE but I am not sure.


Function CountMembers() As Integer
'-------------------------------------------------------------------
' Return the number of members in the tblMember table
'-------------------------------------------------------------------
    Dim dbCurrent As Database
    Dim rsMembers As Recordset
    Dim strSQL As String
    ' Form the SQL string
    strSQL = "SELECT Count(tblServiceMembers.PersonnelID) AS CountMembers" & _
             "FROM tblServiceMembers" & _
             "WHERE (((tblServiceMembers.DELStatus) Like "Active"));"
   
    Set dbCurrent = CurrentDb
    ' Populate a recordset using the SQL string
    Set rsMembers = dbCurrent.OpenRecordset(strSQL)
    If (IsNull(rsMembers.RecordCount) = True) Or (rsMembers.RecordCount = 0) Then
        ' No attendance records were returned for the Member
        CountMembers = 0
    Else
        ' Did the Member attend any meetings?
        rsMembers.MoveFirst
        CountMembers = rsMembers.Fields(0)
    End If
    rsMembers.Close
End Function
John SheehySecurity AnalystAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
you need to provide space before "FROM" and before "WHERE" and  wrap word "ACTIVE" with single quotes
0
 
Rey Obrero (Capricorn1)Commented:
replace this

 strSQL = "SELECT Count(tblServiceMembers.PersonnelID) AS CountMembers" & _
             "FROM tblServiceMembers" & _
             "WHERE (((tblServiceMembers.DELStatus) Like "Active"));"

with

 strSQL = "SELECT Count(tblServiceMembers.PersonnelID) AS CountMembers " & _
             "FROM tblServiceMembers " & _
             "WHERE (((tblServiceMembers.DELStatus) Like 'Active'));"
0
 
John SheehySecurity AnalystAuthor Commented:
Sure enough, that's what it was.  Thank you very much.
0
 
John SheehySecurity AnalystAuthor Commented:
Fastest solution I have ever received
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.