locked
Undefined Function error RRS feed

  • 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


    • Edited by Islandz Friday, October 17, 2014 2:27 PM
    Friday, October 17, 2014 2:25 PM

Answers

  • 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

    Friday, October 17, 2014 2:32 PM
  • 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


    • Edited by KCDW Friday, October 17, 2014 4:56 PM
    • Marked as answer by Luna Zhang - MSFT Thursday, October 23, 2014 8:42 AM
    Friday, October 17, 2014 4:55 PM
  • 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

    Friday, October 17, 2014 7:44 PM
  • 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

    Friday, October 17, 2014 7:57 PM

All replies

  • 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

    Friday, October 17, 2014 2:32 PM
  • 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


    • Edited by Islandz Friday, October 17, 2014 3:07 PM
    Friday, October 17, 2014 3:06 PM
  • 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


    • Edited by KCDW Friday, October 17, 2014 4:56 PM
    • Marked as answer by Luna Zhang - MSFT Thursday, October 23, 2014 8:42 AM
    Friday, October 17, 2014 4:55 PM
  • 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

    Friday, October 17, 2014 7:44 PM
  • 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

    Friday, October 17, 2014 7:57 PM
  •     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.

    Friday, October 17, 2014 8:09 PM