Undefined Function error

Question

I am trying to create a function that I call through an Update query but I seem to be running an issue when I try and Run the query. The error I get is: Undefined function 'riskcat' in expression.  This function is supposed to take the value of two fields in a table, and based on the IF statements, update the new field with a value.  This is actually my first VBA Function, so I'm probably missing something basic, but cannot seem to figure it out, when I compare it to functions a couple of others in my org have written.

Here is the full code:

Function riskcat(f As Integer, c As Integer) As String

On Error GoTo ERR_riskcat

    If IsNull(f) Or IsNull(c) Then
        riskcat = Null
        Exit Function
    End If

    Dim rc As String

    If f = 1 Or 2 Or 3 And c = 1 Or 2 Or 3 Then
        rc = "A"
    ElseIf f = 1 Or 2 And c = 4 Then
        rc = "A"
    ElseIf f = 4 And c = 1 Or 2 Then
        rc = "A"
    ElseIf f = 1 Or 2 And c = 5 Then
        rc = "B"
    ElseIf f = 3 Or c = 4 Or 5 Then
        rc = "B"
    ElseIf f = 4 And c = 5 Then
        riscat = "D"
    ElseIf f = 5 And c = 4 Or 5 Then
        rc = "D"
    Else
        rc = Null
    End If
        
    riskcat = rc
    
    Exit Function
    
ERR_riskcat:

    MsgBox "Error in strippOff; Err.Description: " & Err.Description

    Exit Function
    
End Function

Thank you all in advance

Answer 1

Classic error, triggered in part by unfortunate MSFT default settings.

Open a code window > Tools > Options, and check Require Variable Declaration. This will fix your problem in future.

To fix the current one, go to the top of every module and add:

Option Explicit

Then use Debug > Compile and fix the problems that are being found.

---

-Tom. Microsoft Access MVP

Answer 2

Hi Tom,

Thank you for the assistance.

So in the VBA window for my current function, I now have:

Option Compare Database
Option Explicit

at the top, then my function code below in its own section.  When I go to Debug, the "Compile Database" option is greyed out.  Is there something else I need to do?

Ahmad

Answer 3

Pick a letter, any letter. Delete It. Add the letter back. Move off the line you edited.

Now Debug \ Compile \ Save.

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 4

Aside from the advice give above, your syntax is all wrong.

This:
If f = 1 Or 2 Or 3 And c = 1 Or 2 Or 3 Then

should be:
If (f = 1 Or f = 2 Or f = 3) And (c = 1 Or c = 2 Or c = 3) Then

Also integer data types cannot be Null. They can only be a number. If there is a chance f or c is null then you have to use Variant data types. And a function typed as a String cannot return a Null so it needs to be a variant as well.

Function riskcat(f As Variant, c AsVariant) As Variant

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 5

The line

           riscat = "D"

is incorrect, and almost certainly should have been

           rc = "D"

If "Option Explicit" were in effect, that error would have been highlighted.  Even if that error is corrected, though, the code is still not doing what you intended, as Bill Mosca pointed out.

HOWEVER, that doesn't explain why, if the code compiled without error before, the function name wasn't recognized in your query.  Is this function defined in a standard module, or did you mistakenly place it in a form, report, or class module?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 6

    If f = 1 Or 2 Or 3 And c = 1 Or 2 Or 3 Then
        rc = "A"
    ElseIf f = 1 Or 2 And c = 4 Then
        rc = "A"
    ElseIf f = 4 And c = 1 Or 2 Then
        rc = "A"
    ElseIf f = 1 Or 2 And c = 5 Then
        rc = "B"
    ElseIf f = 3 Or c = 4 Or 5 Then
        rc = "B"
    ElseIf f = 4 And c = 5 Then
        riscat = "D"
    ElseIf f = 5 And c = 4 Or 5 Then
        rc = "D"
    Else
        rc = Null
    End If

Hi Islandz,

Instead of having all these conditions in code, I would use a table with three fields (f, c, rc) to store that information.

The advantage is that whenever you have to change the conditions, it is simply a change in a table instead of a change in the code. As it is meta data, you can store the table in the front end.

Imb.