Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 12th, 2009, 08:57 PM
Registered User
 
Join Date: Feb 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help! Undefined function <....> in expression

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
 
Old February 13th, 2009, 12:43 PM
Registered User
 
Join Date: Jun 2008
Posts: 6
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

daniel
 
Old February 13th, 2009, 11:41 PM
Registered User
 
Join Date: Feb 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 15th, 2009, 02:51 PM
Authorized User
 
Join Date: Jan 2006
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You are aware that the name of a standard Module should *never* be a name of one of your function, right?
__________________
HTH RuralGuy (RG for short) aka Allan Bunch MS Access MVP 2007 acXP WinXP Pro
 
Old February 15th, 2009, 11:40 PM
Registered User
 
Join Date: Feb 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by RuralGuy View Post
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
 
Old February 20th, 2009, 08:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division





Similar Threads
Thread Thread Starter Forum Replies Last Post
Undefined function 'Left' in expression Morris Access 13 July 23rd, 2008 02:07 AM
Message> in query expression <expression>. (Error ybg1 Access 5 July 15th, 2007 05:42 AM
Undefined function 'Left' in expression MountainProud Access 4 October 23rd, 2006 10:48 AM
Regular Expression to remove <table> </table> tags mathalete CSS Cascading Style Sheets 2 January 23rd, 2006 01:59 PM
<undefined value> Scott Rider General .NET 5 June 7th, 2005 01:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.