creating a function help

[Nandeep]

Hi,
I'm very new to using access VBA and functions etc so please bear in mind I may need things explaining very simply.

I have a variable called "Output random number" which has values from 0 to 1000. I need to group these into 20 bucket as such
0 -49 =1
50 -99 = 2
100 -149 =3
.
.
.
950 -1000 =20
I cant do this in an access query as it find it too complex to have so many nested if statements

I believe I should be able to create a function to do this but I do not know how to do this.

Please could anyone help?

 

[Uncle Gizmo]

Do you mean that if you calculate a random number between 0 and 49 you want the output to be 1, if you calculate the random number between 50 and 99 you want the output to be 2 and so on?

 

[Nandeep]

Do you mean that if you calculate a random number between 0 and 49 you want the output to be 1, if you calculate the random number between 50 and 99 you want the output to be 2 and so on?

Hi the random number output is variable is already populated as number between 0 to 1000. Yes based on the value of this variable under the conditions explained above I'd like to allocate a number of between 1 to 20 for them. Hope that makes sense

 

[Minty]

You could simply divide it by 50 ? A calculated field something like

MyResult : Int(([YourRandomNumber] +1) / 50) + 1

I'm not sure your maths is correct though?

 

[Nandeep]

You could simply divide it by 50 ? A calculated field something like

MyResult : Int(([YourRandomNumber] +1) / 50) + 1

I'm not sure your maths is correct though?

Hi,

that may work but I wanted to create a function to do the allocating and then call the function in my query. I have done it before but I've totally forgot how I did it

 

[MajP]

Public Function FunctionName(Num as Integer) as Integer
  FunctionName = Int((Num +1) / 50) + 1
end Function

Select fieldName, FunctionName([Field2Name]) as RangeValue ... from someTable

 

[Minty]

Well the function will be slower but if you really need it you can achieve it lots of ways, You could use a select case statement;

Function BoundNumber(lngInput As Long) As Long
    
    Select Case lngInput
    
        Case 0 - 49
            BoundNumber = 1
        Case 50 - 99
            BoundNumber = 2
        Case 100 - 149
            BoundNumber = 3
      [COLOR="SeaGreen"]  'etc etc[/COLOR]
        
        Case Else
            BoundNumber = 0
    End Select
    

End Function

Store this in a module call it modFunctions or something similar but don't call it BoundNumber!

In your query then use

MyResult : fnBoundNumber([YourRandomNumber])

 

[Nandeep]

Well the function will be slower but if you really need it you can achieve it lots of ways, You could use a select case statement;

Function BoundNumber(lngInput As Long) As Long
    
    Select Case lngInput
    
        Case 0 - 49
            BoundNumber = 1
        Case 50 - 99
            BoundNumber = 2
        Case 100 - 149
            BoundNumber = 3
      [COLOR=seagreen]  'etc etc[/COLOR]
        
        Case Else
            BoundNumber = 0
    End Select
    

End Function

Store this in a module call it modFunctions or something similar but don't call it BoundNumber!

In your query then use

MyResult : fnBoundNumber([YourRandomNumber])

Hi, i've inserted the above code in and then also added to my query but it always outputs the Case else value which is 0 in the code above. Have I made an error in the code somewhere?

 

[MajP]

950 -1000 =20

That will be a big select case. Look at 6 or put the ranges in a table.

bottomRange TopRange Return
0                      49          1
....
950                  999         20

Select field1, Return, from MyTable, Where [Output Random Number] between bottomRange and TopRange

Or use a dlookup on the table.

 

[Minty]

I'd use the simple maths I suggested to start with either in the query or in the function as MajP used.

The select case I put up isn't complete - notice I put in etc etc, you'll have type up the other 17 + Case statements. I was simply demonstrating an alternative technique.

 

[MajP]

If your cases are not symmetric and you cannot use the simple math, put your cases in a table (easier to code and update) then you can use pure sql or you can use a dlookup

Public Function FunctionName(Num as Integer) as Integer
dim strWhere as string
strWhere = "BottomRange <= " & Num & " AND TopRange >= " & Num
FunctionName = nz(dlookup("return","tblRanges,strWhere),0)
end Function