Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Count of records with IT in their title

Posted on 2013-06-05
8
Medium Priority
?
223 Views
Last Modified: 2013-06-05
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
0
Comment
Question by:John Sheehy
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39222705
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
 

Author Comment

by:John Sheehy
ID: 39222718
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
 
LVL 5

Expert Comment

by:sriramiyer
ID: 39222764
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 39222832
Check the spelling of your field names.
0
 

Author Comment

by:John Sheehy
ID: 39223098
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 39223143
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
 

Author Comment

by:John Sheehy
ID: 39223180
Now that works a heck of a lot faster.  Thank you
Points well earned.

John
0
 

Author Closing Comment

by:John Sheehy
ID: 39223185
The answer was perfect and worked a heck of a lot faster then what I was expecting.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question