Link to home
Start Free TrialLog in
Avatar of John Sheehy
John SheehyFlag 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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of John Sheehy

ASKER

Sure enough, that's what it was.  Thank you very much.
Fastest solution I have ever received