Sacha Walter
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much for the explanation, it works great!
ASKER
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.....