Avatar of John Sheehy
John Sheehy
Flag for United States of America asked on

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
Microsoft Access

Avatar of undefined
Last Comment
John Sheehy

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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'));"
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John Sheehy

ASKER
Sure enough, that's what it was.  Thank you very much.
John Sheehy

ASKER
Fastest solution I have ever received
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy