Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Undefined Function (Access 2000 and 97)

    Wow, I'm having a hard time trying to figure this out,
    I have a function that lets the user know when the next anniversary date of a given date will be, this function works fine using Access 97 under Windows 98, Windows 2000, but under Windows XP running Access 97, I receive undefined function 'Next Anni' in expression when you run the query that contains this function. If I go to the immediate window pane and run the same function, it will work properly. Has anyone run into this problem before if so how did you get around this problem?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Undefined Function (Access 2000 and 97)

    Apparently, there is something about the expression in the query that Access doesn't like, perhaps somehow something got into it during conversion. It sounds simplistic, but sometimes just deleting the expression and typing it in again may help. Also, try using that function in another query, just to see if Access likes it there.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undefined Function (Access 2000 and 97)

    You could try posting the code for the function here. Maybe someone else can spot the potential problem with Windows XP. Also specify what regional language settings you're using in WinXP.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (Access 2000 and 97)

    You might check for missing references. Module, Design, Tools, References.

    Uncheck any missing references and try running the function again.

  5. #5
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (Access 2000 and 97)

    Ok, this is the function I'm using, its pretty streight foward.

    Function NextAnni(dtmDate As Date) As Date
    ' Find the next anniversary of a date.
    ' dtmDate:
    ' A date representing anniversary.
    ' Return Value:
    ' The next occurence of the specified date. If the date hasn't
    ' occurred yet this year, return the date within the
    ' current year. Otherwise, return the date in the next
    ' year.

    Dim dtmThisYear As Date

    ' What's the corresponding date in the current year?
    dtmThisYear = DateSerial(Year(Now), Month(dtmDate), Day(dtmDate))

    ' If the anniversary has already occurred, then add 1 to the year.
    If dtmThisYear < Date Then
    dtmThisYear = DateAdd("yyyy", 1, dtmThisYear)
    End If
    NextAnni = dtmThisYear
    End Function

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Undefined Function (Access 2000 and 97)

    There's nothing wrong with the function itself. The "Undefined Function" error when trying to open query results from missing references, as Tom Weller noted in his reply. See this MSKB article, it refers to Access 2002 but the information re undefined function error message is equally applicable for other versions of Access:

    ACC2002: "Undefined Function in Expression" Error Message When You Open a Query That References a Function

    Recommend check for missing references in your project.

    HTH

  7. #7
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (Access 2000 and 97)

    I checked the references and none of them are showing as missing, I also deleted the test information re-added it and it still give me the same problem. Could this be a corrupt installation on the users side?

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Undefined Function (Access 2000 and 97)

    If the computer on which program is running is missing any of the project's referenced type library files as listed in References, then the error will occur, even if query works OK on your computer. AFAIK all the VBA date functions (DateAdd, etc) used by the custom function were valid in A97. I no longer use Access 97 so have no way of testing whether running A97 under WIN XP is cause of problem; maybe someone with this configuration can determine if this is a factor or not.

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (Access 2000 and 97)

    Can you post a cut down version of the db with the function, a query and where you are trying to run it from?

    I've got XPP and A97 that I can check it on and see if I can see any problems.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undefined Function (Access 2000 and 97)

    We run A97 under WinXP on some of the machines at work without any problems. There has to be a reference in the application that doesn't exist on the XP machine. Are you trying to send email from the application by any chance, or referencing the CDO/MAPI library? That doesn't get installed on a WinXP machine unless you install Outlook with some custom settings.
    Charlotte

  11. #11
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (Access 2000 and 97)

    Sorry for the delay in posting, been spending late hours at work. Ok, I've attached a copy of the application when you start up the app it will come to a main form choose Find Anniversary, before the form opens you should get the error, the query its running is titled qryfindcontacts. If any one find out anything please let me know. Thank!
    Attached Files Attached Files

  12. #12
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (Access 2000 and 97)

    Hello Charlotte,
    Thanks for your response, I double checked my references and there is no sign of anything missing, each check mark is referencing an actual item, the app is actualy a very simple program, nothing out of the norm. I attached a copy of the app on earlier post, your welcome to dowload it and see if you have the same problem as user is having. I would love to get this resolved.
    Thanks!

  13. #13
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Undefined Function (Access 2000 and 97)

    Thanks for your responce Tom, but sorry no missing references have been found.

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undefined Function (Access 2000 and 97)

    Lose the Extensibility Library reference. I didn't get a compile error when I unchecked it, but it will cause you problems if that file isn't available on the machine. There are other extensibility libraries and on my machine both of them are for VB/VBA 6 rather than the VB/VBA 5 version you had checked. It doesn't come up as missing, on my PC, but I wouldn't expect to find it on a machine that had never had Office 97 or VB5 installed.
    Charlotte

  15. #15
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Undefined Function (Access 2000 and 97)

    To open files, I had to convert them to A2K format (running AXP) & got conversion errors - the Reminder_be.mdb file conversion error reads "Missing or broken VBE reference to the file 'dao2535.tlb' - see attd error msg (this is reference to MS DAO 2.5/3.5 Compatability Library). I have only AXP installed on this computer. The references in front end Reminder.mdb seemed OK. I did not get error when opening frmListOfAnv form or qryFindContacts query. The NextAnni function worked OK. As for the query, this has to be the most convoluted query I've ever seen. I'm surprised it works at all, "undefined function" error or not. Its based on two other queries, and its length is 14579 characters, nearly 1/4 of the maximum of "approximately 64,000" for "Number of characters in an SQL statement" as stated in Access query limitations. There's also a limitation of 99 "AND"s in a WHERE or HAVING clause but who's counting....

    I'd recommend getting rid of any "Compatability" or "Extensibilty" references in both front end & back end & try to somehow simplify the overly convoluted query.

    HTH
    Attached Images Attached Images

Page 1 of 2 12 LastLast

Posting Permissions

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