Best method to map and populate a new field that is based on a range of values in an existing field

[fred.s...@gmail.com]

Hi - I am trying to assign or map a score in a field called "Weight" of 1 to 5, based on an existing field (called Rank) that has values of 1 to 4 million.

So, for example, Values in the 'Rank' Field that range from 1 to 100,000 need to be assigned a value of 5 in the 'Weight' field, and Values in the 'Rank' Field that range from 100,000 to 500,000 need to be assigned a value of 4 in the 'Weight' field, etc.

In Excel, I am able to accomplish this using the Lookup Function or by using the Index and Match function.

However, I am trying to determine the best method to do this in Access. I suppose I could develop a very long IIF statement, but I was hoping there is a simpler way that would allow me to create a table with the ranges, and then somehow populate the results in the field titled "Weight" by running an update query (or something like that).

Does anyone know of a simple and efficient way to do this? Thanks for any advise.

[GLepage]

Use the following code in the form module attached to a calculate button object.

Select Case Me.Rank
Case 1 To 100000
Me.Weight = 1
Case 100001 to 500000
Me.Weight = 2
(etc.....)
End Select