Access 97 Error 3085: Undefined function

[AFL]

I link to external data base files via ODBC. I have queries that link and sort the raw data. I have a VBA function (written within Access Modules) that takes three text fields from a query, performs some simple manipulations, and then returns a string. The function compiles and saves without errors. Within the query I use the Expression builder to call the function I wrote. Within the Expression Builder, the procedure is visible and appears in order and I can select the query's fields as the function's arguments. However, when I select the "View Data" icon I get the following message: "Undefined function function-name in expression". When I click the error message "Help" icon I get:
Undefined function <name> in expression. (Error 3085).
You entered an SQL expression that includes a Function
procedure name that can't be recognized. Make sure the
function exists, that it can be used in SQL expressions,
or check the expression to make sure you entered the name
correctly.
Based on the Access help files I looked at, it appears there may be a conflict between OBDC and Microsoft Jet workspace types. Not being a power-user or programmer, is there a way to resolve this? I would really appreciate any help. Thanks in advance.

 

[jebry]

Hi!

That error usually indicates that you left out the keyword Public when defining the function. If that isn't the problem, maybe you can post the function and the SQL from the query so we can try to see what is wrong.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[AFL]

Problem solved. I upgraded to Access 2000 and rebuilt the database. I believe I had a corrupted database. Upgrade probably wasn't necessary but I like 2000 much better.

 

[drjmh]

I have a similar problem in Access97. We are in the slow process of converting to Access2000. Until all is completed we still develop in Access97 so converting this specific database to Access2000 is not an option.

The query with the function in it can run ok by opening the query itself, but when envoked through VBA code I get this runtime error 3085, undefined function. I have other queries using this same function that run OK in code. This query and a few others intermittently do not run in code. I am not using ODBC. Everything is Access97. The other day I got this query to run in code by cutting the function from Module1 and pasting it in the form module. Then cutting it from the form module and pasting it back into Module1. It worked for a couple days. Today it is not working in code but can still run by itself. Today cutting and pasting does not do the trick.

Any ideas?