Solved

Count of records with IT in their title

Posted on 2013-06-05
8
215 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

685 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