John Sheehy
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.Pe rsonnelID) AS CountMembers" & _
"FROM tblServiceMembers" & _
"WHERE (((tblServiceMembers.DELSt atus) Like "Active"));"
Set dbCurrent = CurrentDb
' Populate a recordset using the SQL string
Set rsMembers = dbCurrent.OpenRecordset(st rSQL)
If (IsNull(rsMembers.RecordCo unt) = 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
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.Pe
"FROM tblServiceMembers" & _
"WHERE (((tblServiceMembers.DELSt
Set dbCurrent = CurrentDb
' Populate a recordset using the SQL string
Set rsMembers = dbCurrent.OpenRecordset(st
If (IsNull(rsMembers.RecordCo
' 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sure enough, that's what it was. Thank you very much.
ASKER
Fastest solution I have ever received
strSQL = "SELECT Count(tblServiceMembers.Pe
"FROM tblServiceMembers" & _
"WHERE (((tblServiceMembers.DELSt
with
strSQL = "SELECT Count(tblServiceMembers.Pe
"FROM tblServiceMembers " & _
"WHERE (((tblServiceMembers.DELSt