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 SheehySystem Security ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rey Obrero (Capricorn1)Commented:
you need to provide space before "FROM" and before "WHERE" and  wrap word "ACTIVE" with single quotes
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John SheehySystem Security ManagerAuthor Commented:
Sure enough, that's what it was.  Thank you very much.
0
John SheehySystem Security ManagerAuthor Commented:
Fastest solution I have ever received
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.