Solved

Count of records with IT in their title

Posted on 2013-06-05
8
213 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access on Mouse move 5 31
Openoffice or opensource excel/word/ppt for Mac OSX Mountain Lion 14 42
VBA - If Bookmark = "XXBOOKMARKXX" then 15 26
Switch 4 10
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

776 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