Undefined Function (Access 2000 and 97)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

Well, I did a netmeeting with the person who is having the problem, and they didn’t have Extensibility Library reference checked, there was only 3 items checked Visual Basic For Applications, Microsoft Access 8.0 Object Library, and Microsoft DAO 3.51 Object Library, if I uncheck Microsoft DAO 3.51 Object Library I don’t get the error however the form still doesn’t work. So as a last resort I asked the user to un-stall and re-install and that fixed the problem, it seem that it was a bad installation. Wow, this one was driving me mad.

One last thing, Mark D. how would you simplify the overly convoluted query??

Well Thank everyone!! for your help I sure learned a few thing.

Reply | Quote

Probably by splitting it into 5 separate queries based upon the different form fields then using a UNION query to bring them all together again.
Pat

Reply | Quote

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

Uncheck any missing references and try running the function again.

Reply | Quote

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

Reply | Quote

Hi

If there was a missing reference, I would expect the behaviour to be the same from the immediate window also, but the function runs fine when run from the immediate window?

A guy i work with had a problem the other day, similar to this, but nothing to do with missing references. When trying to use the ‘replace’ function from a query in Acc 2000, he got the same error message, although, when using it from VBA there was no problem. The only solution was to wrap the replace function inside a custom function and call the custom function! it appears that some in-built functions in Acc do not work directly from queries.

Although this is not exactly the same situation as you, it sounds a little bit similar, but you are calling a custom function from a query.

I don’t know how much help this is, but it might ‘jog’ someone elses memory about this problem?

Cheers

Reply | Quote

Some queries can become so complex that they will run as a query from the user interface but not as the source for a report. Reports generate temporary queries when they are opened, so if a query is just on the edge of too complex, using it as a report recordsource will tip it over.

Depending on how a function is called from a query, it may run over and over again, and some functions aren’t really appropriate to calling in queries. I would put Replace in that category because of the nature of what it does. If the query affected a small number of records, no problem. If it affected a large number of records, unusable.

Reply | Quote

Some funtions introduced in VB/VBA 6.0 do not always “function” correctly if you try to use them in a query expression in Access 2K. It may be because the A2K query engine wasn’t updated to recognize these new functions?? In the case of the Replace function, according to the Access 2000 Readme file (Acread9.txt):
[indent]

---

Functions Broken Because Of vbDatabaseCompare
———————————————
The InStrRev, Split, Filter, and Replace functions may fail because of errors resulting from the use of vbDatabaseCompare. The workaround is to explicitly specify a comparison argument, for example, vbBinaryCompare or vbTextCompare.

---

[/indent]
(Note – all of above were introduced in VB/VBA 6.0.) If using one of these functions in query expression, you cannot use named constant for optional Compare argument, you have to use actual value. These are:

Const vbBinaryCompare = 0
Const vbTextCompare = 1
Const vbDatabaseCompare = 2

The default for all 4 functions is vbBinaryCompare (case-sensitive comparison). vbTextCompare indicates case-insensitive comparison. You can try to use function in query, explicitly specifying numerical value for Compare argument, and see if it works. I do not have A2K installed on my PC so I cannot test this; these functions work correctly when used in query expressions in AXP. Otherwise, as noted, wrap function in a user-defined function which can then be used in a query.

HTH

Reply | Quote

Just as an aside, you *always* have to use the actual values of constants in queries. Queries never recognize system constants, even the built-in constants.

Reply | Quote

Just when I thought I had this problem fixed, the user goes out and re-boot his PC and it starts all over again. As it turns out un-instaling and re-install only keeks the program running correctly as long as you don’t re-boot after re-booting the problem start all over again!
Is there no end to this madness??

Reply | Quote

Excuse me, but that doesn’t make any sense. Rebooting the machine shouldn’t change the libraries that are on it unless some of his references are on network drives or removeable drives which are not always available. If that is the case, all bets are off because Access isn’t really designed to be run that way. Is there something about that installation you haven’t told us? Is Access running on Transaction Server or some kind of virtual machine rather than local to his workstation?

Reply | Quote

Sorry Charlotte, but these are the facts, the user is using Windows XP Home Edition with MS Office 97 in their home and it isn’t on a network. The installation was a standard one, I’ve even checked and made sure that all of the service packs were install. I guess my next step is to make a post in the Win XP forum to see if this happens to be a windows admin issue or what have you.
Do you have any recomendations?

Reply | Quote

Have you checked to see what service packs have been applied to Office? There were some insidious problems with Access 97 running on Windows 2000 PCs that were solved with service packs for both Office and Windows. It’s sort of a shot in the dark, but at this point it sounds like you are sort of stumped.

Reply | Quote

I checked the service packs and the user has installed them all.

Reply | Quote

Thanks for responding Wendell, I checked to see if the user had installed the service packs and I found that they had all of them installed.

Reply | Quote