Link to home
Start Free TrialLog in
Avatar of Sacha Walter
Sacha WalterFlag for Canada

asked on

Access 2010 VBA Case Select Function

Hi Experts,

I tried to create a function which utilizes a number scale to convert a value to a mapped number as follows:

Function IGMap()
Dim IG As Double
Dim Map As Double
 
     Select Case IG
        Case Is < 16
            Map = 15
        Case Is >= 16 And IG < 18
            Map = 17
        Case Is >= 18 And IG < 20
            Map = 19
        Case Is >= 20 And IG < 21.5
            Map = 21
        Case Is >= 21.5 And IG < 23.5
            Map = 22
        Case Is >= 23.5 And IG < 26
            Map = 25
        Case Is >= 26 And IG < 28.5
            Map = 27
        Case Is >= 28.5 And IG < 35
            Map = 30
        Case Is >= 33 And IG < 50
            Map = 40
        Case Is >= 50 And IG < 62.5
            Map = 60
        Case Is >= 62.5 And IG < 67.5
            Map = 65
        Case Is >= 67.5 And IG < 71.5
            Map = 70
        Case Is >= 71.5 And IG < 74
            Map = 73
        Case Is >= 73 And IG < 76
            Map = 75
        Case Is >= 76 And IG < 78.5
            Map = 77
        Case Is >= 78.5 And IG < 81.5
            Map = 80
        Case Is >= 81.5 And IG < 84
            Map = 83
        Case Is >= 84 And IG < 86
            Map = 85
        Case Is >= 86 And IG < 88.5
            Map = 87
        Case Is >= 88.5 And IG < 92.5
            Map = 90
        Case Is >= 92.5 And IG < 96.5
            Map = 95
        Case Is >= 96.5 And IG < 98.5
            Map = 98
        Case Else
            Map = 99
        End Select
    IGMap = Map

End Function

When I use IGMap() in the criteria of a query, the query will run, but will return no values.  If I try instead to input a field value such as:  Test:  IGMap([field]) I get the error message 'The expression you entered has a function containing the wrong number of arguments.'

Any help on what I am doing wrong would be appreciated.

Thanks!
SOLUTION
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sacha Walter

ASKER

Great, thanks...only remaining problem is that when I try Test:  IGMap([field]) it returns 17 for all of my values.

For example if the [field] value is 95.6 the returned value should be 95 (not 17), a [field]value of 81.25 should return 80 (not 17), etc.....
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks so much for the explanation, it works great!