Undefined Function (Access 2000 and 97)

[unpuertomex]

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?

[MarkLiquorman]

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.

[charlotte]

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.

[taweller]

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

Uncheck any missing references and try running the function again.

[unpuertomex]

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

[MarkD]

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

[unpuertomex]

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?

[MarkD]

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.

[carbonnb]

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.

[charlotte]

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.

[unpuertomex]

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!

[unpuertomex]

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!

[unpuertomex]

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

[charlotte]

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.

[MarkD]

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