Results 1 to 8 of 8
  1. #1
    killermonkey is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    20

    Select case help

    Trying to convert some numbers into a range. I tried copying and modifying previous code I've used but it isn't working out. Basically what I'm trying to do is group scores into categories like all scores between 80 and 89.99 will be considered 80. I want to do this so I can make a aggregate query that groups them together and provides me a count of each.

    Problem is I keep getting the "too few parameters expected" error

    Here is the code I'm using and thank you in advance for any help you can provide.

    Code:
    Public Function iGroup(iScore As Single) As Integer
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim iRange As Integer
    
    Set dbs = CurrentDb
       
        'convert scores to ranges
        Select Case iScore
        Case 100
            iRange = 100
        Case 90 To 90.99
            iRange = 90
        Case 80 To 89.99
            iRange = 80
        Case 70 To 79.99
            iRange = 70
        Case 60 To 69.99
            iRange = 60
        Case 50 To 59.99
            iRange = 50
        Case 40 To 49.99
            iRange = 40
        Case 30 To 30.99
            iRange = 30
        Case 0 To 29.99
            iRange = 29
        End Select
    
    Set rst = dbs.OpenRecordset("PFTPoints")
    If rst.BOF And rst.EOF Then
        iGroup = 0
    Else
        iGroup = rst("Score")
    End If
        
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
        
         
           
    End Function


  2. #2
    killermonkey is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    20
    Ignore the dim sSQL. I took that out in an attempt to get it to work.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I don't understand this

    If rst.BOF And rst.EOF Then

    Maybe you could have

    If rst.EOF = false Then

    And you will need to do something with your rst like .movefirst .movenext.then you can collect information about the recordset you are on.

    Instead of

    iGroup = rst("Score")

    You would assign the value to iGroup like

    iGroup = rst![Score]


    Having said that, you are still far from getting the results because you need to do "Something" with the result. You are looping through so each time you get to a record you need to update a field somewhere. this qould require a second "Nested loop". Unless I just don't understand what your end goal is.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I saw these:
    "Case 90 To 90.99" should be "Case 90 To 99.99"

    "Case 30 To 30.99" should be "Case 30 To 39.99"



    The reason you might be getting the error is you opened a recordset on a table. If there were records in the table, you tried to set iGroup = to a field that is not in the recordset.
    The function I wrote for you only returns 1 value, a "score".
    You will need to write a different function or use a totals query to get a count of each range.


    You said:
    I want to do this so I can make a aggregate query that groups them together and provides me a count of each.
    Can you explain what you want to query to do?
    What are you going to do with the counts? Display in a text box, in a report?

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    That can be done an easier way. Instead of mucking about with VBA, use code like this in your query to dynamically calculate the group. Replace the [MyScore] variable with your own variable name.
    Code:
    IIF([Myscore]<30,29,10*Fix([MyScore]/10)) AS ScoreGroup

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I tend to muck about with VBA (probably more that I should), but that is a carryover from dBase III procedural programming.

    I came up with a query that calculates the number of points in each range.

    comments?
    Code:
    SELECT Sum(P1.P20) AS SumOfP20, Sum(P1.P30) AS SumOfP30, Sum(P1.P40) AS SumOfP40, Sum(P1.P50) AS SumOfP50, Sum(P1.P60) AS SumOfP60, Sum(P1.P70) AS SumOfP70, Sum(P1.P80) AS SumOfP80, Sum(P1.P90) AS SumOfP90, Sum(P1.P100) AS SumOfP100
    
    FROM (SELECT PUPoints.Points, IIf([Points]<30,1,0) AS P20, IIf([Points]>=30 And [Points]<40,1,0) AS P30, IIf([Points]>=40 And [Points]<50,1,0) AS P40, IIf([Points]>=50 And [Points]<60,1,0) AS P50, IIf([Points]>=60 And [Points]<70,1,0) AS P60, IIf([Points]>=70 And [Points]<80,1,0) AS P70, IIf([Points]>=80 And [Points]<90,1,0) AS P80, IIf([Points]>=90 And [Points]<100,1,0) AS P90, IIf([Points]=100,1,0) AS P100
    FROM PUPoints) AS P1;

    (Still working on learning advanced SQL/query creation)

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I assume you have figured out that this part:
    Code:
    Set rst = dbs.OpenRecordset("PFTPoints")
    If rst.BOF And rst.EOF Then
        iGroup = 0
    Else
        iGroup = rst("Score")
    End If
    ...goes *before* the Select clause, right? And the Select structure should be setting the iGroup rather than the iRange. Keeping in mind the recommendations of ssanfu of course.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The above function is killermonkey's attempt to modify a function I wrote for him a week(?) ago. The function returned a value (Score) from a look up table (not field)
    based on the Gender, Age and number of pushups/situps/run time/walk time.

    That function (or the attempted modification) will not work for the question posted in this thread.

    I think the object is to take the total points (scores) from the pushups/situps/run time/walk time... and see how many people are in which point range.
    So I made a query that might work. I know Dal has ideas on the query. (Or maybe VBA..... Hahahahah)

    I'm just waiting for killermonkey to respond.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA for SELECT CASE logic
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 12-04-2012, 09:09 PM
  2. select case problem
    By Mclaren in forum Programming
    Replies: 3
    Last Post: 11-17-2011, 01:28 PM
  3. select case or else if for unhiding
    By nichmeg in forum Programming
    Replies: 3
    Last Post: 10-30-2011, 09:30 AM
  4. Select Case vs Dlookup
    By BRV in forum Programming
    Replies: 1
    Last Post: 10-28-2011, 03:18 PM
  5. Help with Select Case statement
    By focosi in forum Access
    Replies: 4
    Last Post: 08-09-2011, 12:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums