Count of records with IT in their title

Below is the SQL I am using to have my app display only the count of records that have IT in their title and have earned the IDW qualification.

strSQL = "SELECT Count(tblMember.COMPASSID) AS MemberCount " & _
             "FROM tblMember " & _
             "WHERE Left$(RankRate, 2 ) = 'IT' and (tblMember.PrimaryWarfareDesignator) Like 'IDW' and ((tblMember.DelStatus)=False)and((tblMember.Civilian)=False);"

Only I keep getting a syntax error on the statement.  

Any thoughts
John SheehySecurity AnalystAsked:
Who is Participating?
 
IrogSintaCommented:
I don't see the need for HAVING since you are not using GROUP BY.  Using LIKE is also unnecessary for PrimaryWarfareDesignator since you are not using a wildcard in your criteria.  You can just use this:
strSQL = "SELECT Count(COMPASSID) AS MemberCount " & _
    "FROM tblMember " & _
    "WHERE Left([Rank/Rate], 2) Like 'IT*' AND PrimaryWarfareDesignator = 'IDW' AND DelStatus=False AND Civilian=False"

Open in new window

0
 
IrogSintaCommented:
I believe it's because you lack spaces before and after one of the AND operators.  Try this simplified version:
strSQL = "SELECT Count(tblMember.COMPASSID) AS MemberCount " & _
    "FROM tblMember " & _
    "WHERE Left(RankRate, 2) = 'IT' AND PrimaryWarfareDesignator Like '*IDW*' AND DelStatus=False AND Civilian=False"

Open in new window

0
 
John SheehySecurity AnalystAuthor Commented:
I get a runtime error 3061
Too few parameters. Expected 1

Here is the whole code

Function CoreIDW() As Integer
'-------------------------------------------------------------------------------------------------------------------
' Return the number of members in the tblMember table that are ITs and have qualified IDW
-------------------------------------------------------------------------------------------------------------------

    Dim dbCurrent As Database
    Dim rsMembers As Recordset
    Dim strSQL As String
    ' Form the SQL string
    strSQL = "SELECT Count(tblMember.COMPASSID) AS MemberCount " & _
    "FROM tblMember " & _
    "WHERE Left(RankRate, 2) = 'IT' AND PrimaryWarfareDesignator Like '*IDW*' AND DelStatus=False AND Civilian=False"
    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
        CoreIDW = 0
    Else
        ' Did the Member attend any meetings?
        rsMembers.MoveFirst
        CoreIDW = rsMembers.Fields(0)
    End If
End Function
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sriramiyerCommented:
why don't you try to get what is inside strSQL using msgbox syntax

msgbox  strSQL

use the above line for debugging purpose. This line should be introduced after
DelStatus=False AND Civilian=False"


i.e.


Function CoreIDW() As Integer
'-------------------------------------------------------------------------------------------------------------------
' Return the number of members in the tblMember table that are ITs and have qualified IDW
-------------------------------------------------------------------------------------------------------------------

    Dim dbCurrent As Database
    Dim rsMembers As Recordset
    Dim strSQL As String
    ' Form the SQL string
    strSQL = "SELECT Count(tblMember.COMPASSID) AS MemberCount " & _
    "FROM tblMember " & _
    "WHERE Left(RankRate, 2) = 'IT' AND PrimaryWarfareDesignator Like '*IDW*' AND DelStatus=False AND Civilian=False"

msgbox  strSQL

    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
        CoreIDW = 0
    Else
        ' Did the Member attend any meetings?
        rsMembers.MoveFirst
        CoreIDW = rsMembers.Fields(0)
    End If
End Function


============

Now after seeing the content of strSQL, you can correct the error easily by you.
0
 
IrogSintaCommented:
Check the spelling of your field names.
0
 
John SheehySecurity AnalystAuthor Commented:
Here is what I ended up using, but you were right there was a mis-spelling.

strSQL = "SELECT Count(tblMember.CompassID) AS MemberCount " & _
             "FROM tblMember " & _
             "WHERE ((tblMember.DelStatus)=False)and((tblMember.Civilian)=False) " & _
             "HAVING ((tblMember.[Rank/Rate]) Like 'IT*') AND ((tblMember.PrimaryWarfareDesignator) Like 'IDW');"

Thanks
J
0
 
John SheehySecurity AnalystAuthor Commented:
Now that works a heck of a lot faster.  Thank you
Points well earned.

John
0
 
John SheehySecurity AnalystAuthor Commented:
The answer was perfect and worked a heck of a lot faster then what I was expecting.
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.