First User-Defined Function (1 Viewer)

I am trying to write my first user-defined function and, of course, things are not going well. The function I wrote is:

Public Function BinData(dValue As Double) As String
If (dValue > 0) And _
(dValue < 1) Then
BinData = "Group 1"
Else If (dValue > 1) And _
(dValue < 2) Then
BinData = "Group 2"
Else If (dValue > 2) And _
(dValue < 3) Then
BinData = "Group 3"
Else If (dValue > 3) And _
(dValue < 4) Then
BinData = "Group 4"
Else If (dValue > 4) And _
(dValue < 5) Then
BinData = "Group 5"
End If

End Function

I pass this function the field name in a table of the calling expression is:

Expr1: BinData([SLRtbl Fuel Ass'y Discharges]![SLR_Init_Enrich])

and was hoping to get this function to assign the value to a category. When I run the function, I get the error message:

"Undefined function in an expression."

I went to the help screen but the message returned was not very useful. Is there anything obvioous that I can do? I expect my error is simple but I have never done this before and the text I have hasn't been very helpful either. Thanks.

Try forcing the datatype you are passing into a Double:

Expr1: BinData(CDbl([SLRtbl Fuel Ass'y Discharges]![SLR_Init_Enrich]))

Also, you can simplify:

Public Function BinData(dValue As Double) As String

Select Case dValue
Case Is < 1: BinData= "Group 1"
Case Is < 2: BinData= "Group 2"
Case Is < 3: BinData= "Group 3"
Case Is < 4: BinData= "Group 4"
Case Is <= 5: BinData= "Group 5"
Case Else: BinData= "No Group"
End Select

End Function

Also, is the function in a standalone module or have you placed it within a form's Class module?

pdx_man said:

Also, you can simplify:

Public Function BinData(dValue As Double) As String

Select Case dValue
Case Is < 1: BinData= "Group 1"
Case Is < 2: BinData= "Group 2"
Case Is < 3: BinData= "Group 3"
Case Is < 4: BinData= "Group 4"
Case Is <= 5: BinData= "Group 5"
Case Else: BinData= "No Group"
End Select

End Function

Click to expand...

One slight change, to reflect the greater than 0 part:

Since your groupings are consistent, it appears
that you could resort to a formula rather than
defining each group individually, e.g.
HTH - Bob, GED