Help! Undefined function <....> in expression

GV_Access_2000 · February 12th, 2009, 08:57 PM

I am trying to use a function in an Access query.
Here is the code in the Module:

Code:

Public Function DeriveStatus(Delivery_DT, Billing_Doc_DT, Order_Qty, Confirmed_Qty)
    If [Delivery_DT] <> 0 Then
       DeriveStatus = "Delivered"
    Else
       If [Billing_Doc_DT] <> 0 Then
          DeriveStatus = "Invoiced"
       Else
          DeriveStatus = "NOT Confirmed"
       End If
    End If
    
End Function

It is invoked in a query field as follows:
Order Status: DeriveStatus([Delivery_DT],[Billing_Doc_DT],[Order_Qty],[Confirmed_Qty])

When trying to run it, it error with: Undefined functin "DeriveStatus" in expression.
Could someone please help me? I am just starting to work on Access.
I followed a thread that mentioned Utility.mda (uncheck it, I think, but I followed the paths suggested and there is no such entry in the references.
Thank you very much!!
GV

dlipkie · February 13th, 2009, 12:43 PM

Try explicitly setting the return type of your function to "As String".

daniel

GV_Access_2000 · February 13th, 2009, 11:41 PM

Daniel,
Thanks for your suggestion. It turned out to be a problem with the name (or linking it to the function in the query, I am not sure). I had the code window and the query open at the same time. I kept changing the name of the function in both places to come up with a more "intelligent" name, to remind me where to go and find this logic. After doing that, invoking the function from the query would give me the error message. I solved it by starting all over with a new module and a new name, then going to the query and using the new name. Everything went back to normal.
But now I am left with a question: if I change the name of a function/module, what do I have to do besides saving it and compiling it, so that it is found/it can be used by other pieces of my Access database?
Thanks again for your help!
Gianfranco

RuralGuy · February 15th, 2009, 02:51 PM

You are aware that the name of a standard Module should *never* be a name of one of your function, right?

GV_Access_2000 · February 15th, 2009, 11:40 PM

Quote:

Originally Posted by RuralGuy

You are aware that the name of a standard Module should *never* be a name of one of your function, right?

RG,
I did not know that, but (beginner's luck?) they were not the same. Thanks!!!
Gianfranco

SerranoG · February 20th, 2009, 08:49 AM

This function doesn't declare any variable types at all. What types are

Delivery_DT & Billing_Doc: Byte, Integer, Long, Boolean (yes/no)?
Order_Qty, Confirmed_Qty: Long?
DeriveStatus: I assumed it's a string

Assuming boolean, boolean, long, long, string:

Code:

Public Function DeriveStatus(Delivery_DT as Boolean,  Billing_Doc_DT as Boolean, Order_Qty as Long, Confirmed_Qty as Long) as String
 
    Dim strStatus as String
 
    If [Delivery_DT] Then
        strStatus = "Delivered"
    Else
        If [Billing_Doc_DT] Then
            strStatus = "Invoiced"
        Else
            strStatus = "NOT Confirmed"
        End If
    End If
 
    DeriveStatus = strStatus
 
End Function