Solved

Count of records with IT in their title

Posted on 2013-06-05
8
211 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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39222832
Check the spelling of your field names.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 500 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now