Create a user-defined lookup function in Access 2010, and call that function in a query

Question

I have been looking thru existing posts and questions to try to find a solution before asking my own question.  I have never created a user-defined function in Access 2010, and obviously then have not yet used/called such a function in my query.  Here is what I am attempting to do:

I have a table containing a field AssessedNeedAmt.  In a query, I am attempting to assign point values based on the value of the AssessedNeedAmt value.

AssessedNeedAmt            Points

12500                                  25

9000                                    22  

400                                      0

8600                                    21

8401                                    21

We were using a similar function in Excel, but are now trying to duplicate such a function in Access where all the records are stored.  This is my attempt thus far, but it is not working.  Perhaps you can tell me where I am making my errors.  Any advice you can give me would be much appreciated.

Public Function NeedPoints(AssessedNeedAmt As Single)

    Select Case AssessedNeedAmt

        Case 10001 To 20000

            Points = 25

        Case 9601 To 10000

            Points = 24

        Case 9201 To 9600

            Points = 23

        Case 8801 To 9200

            Points = 22

        Case 8401 To 8800

            Points = 21

        Case 8001 To 8400

            Points = 20

        Case 7601 To 8000

            Points = 19

        Case 7201 To 7600

            Points = 18

        Case 6801 To 7200

            Points = 17

        Case 6401 To 6800

            Points = 16

        Case 6001 To 6400

            Points = 15

        Case 5601 To 6000

            Points = 14

        Case 5201 To 5600

            Points = 13

        Case 4801 To 5200

            Points = 12

        Case 4401 To 4800

            Points = 11

        Case 4001 To 4400

            Points = 10

        Case 3601 To 4000

            Points = 9

        Case 3201 To 3600

            Points = 8

        Case 2801 To 3200

            Points = 7

        Case 2401 To 2800

            Points = 6

        Case 2001 To 2400

            Points = 5

        Case 1601 To 2000

            Points = 4

        Case 1201 To 1600

            Points = 3

        Case 801 To 1200

            Points = 2

        Case 401 To 800

            Points = 1

        Case 0 To 400

            Points = 0

        End Select

End Function

Answer 1

Create a standard module by selecting Insert > Module in the Visual Basic Editor.

Copy the following code into the module:

Function NeedPoints(AssessedNeedAmt As Variant) As Variant

    If IsNull(AssessedNeedAmt) Then

        NeedPoints = Null

    ElseIf Not IsNumeric(AssessedNeedAmt) Then

        NeedPoints = Null

    ElseIf AssessedNeedAmt > 10000 Then

        NeedPoints = 25

    Else

        NeedPoints = (AssessedNeedAmt - 1) \ 400

    End If

End Function

In the query based on the table, create a calculated column:

Points: NeedPoints([AssessedNeedAmt])

Answer 2

Awesome!  Thanks so much.  Your solution is so much easier than my attempt!  It worked perfectly.