Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2004
    Location
    Globalized
    Posts
    14

    Unanswered: Error: Undefined function in expression

    I have been asked to look after an Access database that has been moved to another user's PC. Apparently it worked fine until the move.

    It is a very simple database with just a few tables and one query. The query refers to a user defined function ( in a module). However, when I try to run the query, it returns the error message "Undefined function in expression".

    I have checked that I have all the reference libraries loaded, eg.

    * Visual Basic for Applications
    * Microsoft Access 9.0 Object Library
    * OLE Automation
    * Microsoft DAO 3.6 Object Library
    * Microsoft ActiveX Data Objects 2.1 Library

    But it just doesn't work, not even on my PC either.

    It's in Access 2000.

    Anything else I need to look into?

    Hope you can help.

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not a lot to go on!

    is the (module) function declared Public?
    if NO: fix it

    if YES:
    put a breakpoint in the function and see if the query gets that far.
    if NO, you likely have a SQL problem: post the SQL.
    if YES, you likely have a reference or api-declaration problem: post the function

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2004
    Location
    Globalized
    Posts
    14
    Function in the module is declared Public.

    Breakpoint shows that when running the query, it never actually enters that function.

    I commented out all code inside the function, and still it does not get to the point of realizing that there is a function of that name in the module. There is actually just that one function in the entire database.

    I also checked the spelling. No problems there. It is totally weird. It runs fine on the original PC where the database was before it was copied to another PC.

    Anything else I can try out?

    Thanks.

    LF

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    From any code window, try Compile And Save All Modules. See if it finds an error.

    Check the Calling mechanism: You know... Call MyFunction(blah, blah)

    Post the Function.

    .............
    Last edited by CyberLynx; 03-29-04 at 00:25.

  5. #5
    Join Date
    Jan 2004
    Location
    Globalized
    Posts
    14
    I compiled and saved the function. No errors. Even wrote an empty function eg. Public test() and no code at all. It is not finding the function.

    LF

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    What in heavens name is calling the function?....there must be something wrong with the call then.

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Open any Module, then start the direct Window, in which you could call functions, type ? theNameOfyourFunction see whether you get an answer or not....

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    going back to my original suggestion: if your query doesn't call the function you have a query problem, not a function problem.

    so post the SQL.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Jan 2004
    Location
    Globalized
    Posts
    14
    OK, here is the SQL query:

    SELECT Shipments.[Active ID], Shipments.Country, Shipments.[Ship Weight], Shipments.Delivered, GetCost() AS [Courier Cost]
    FROM [Courier Names], Shipments, Zones;



    The error refers to the GetCost() function.

    I hope this clarifies it.

    Thanks.

    LF

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...but [courier cost] must surely depend on country or weight or something... how is GetCost() supposed to determine these factors?

    perhaps GetCost is expecting GetCost(zoneID as long, weight as long) or something similar... perhaps the function call fails because you are not passing required parameters to the function?

    sorry i don't see the solution yet!
    now i need the XXXX and YYYY from:
    public function GetCost(XXXXXX) as YYYY

    plus any ZZZZ that the function is hoping to get from elsewhere other than it's own constants


    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Jan 2004
    Location
    Globalized
    Posts
    14

    Unhappy

    I have pulled out the paramaters for GetCost function to see if the problem is with the function code or some other cause. Since the function now contains no code, and is not expecting any parameters at all and it is still giving me the undefined function error, surely the problem cannot be with the function since there is nothing in that function to go wrong.

    At least this is my experience in programming in contexts other than Access. I take out the function body to see if there is any function call at all. If so, return the code into the function and take it that the problem is with the function code not the call to it.

    I just don't seem to understand Access peculiarities if it still complains about a function being undefined if that function is in the module and is plublic and is correctly called in the SQL query. A mere test function with no parameters in the definition and no parameters in the calling code should just run. Right?

    The test function in the module now looks like this,

    Public Function GetCost()

    ' code commented out for testing

    End Function

    Given that it's now that simple, why the undefined function problems?


    LF
    Last edited by LostFrog; 03-30-04 at 17:40.

  12. #12
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    If you take the GetCost() call out of the query, does it still error ?

  13. #13
    Join Date
    Jan 2004
    Location
    Globalized
    Posts
    14
    If I take out the GetCost function from the SQL query it works fine.

  14. #14
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    And the GetCost function, right now, esentially look like this:

    Function GetCost() as Variant

    Stop

    End Function

  15. #15
    Join Date
    Jan 2004
    Location
    Globalized
    Posts
    14
    It looks exactly like this,

    Public Function GetCost()

    ' code commented out for testing

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •