Undefined Function in Query

[Parker]

I have a Public Function (call it MyFunction) that I need to call from a query but I keep getting 'Undefined Function in Expresion'

Here is the relevent part of the SQL

SELECT tblMember.String_Value, IsNumeric([tblMember]![String_Value]) AS Sort1, IIf(IsNumeric([tblMember]![String_Value]),MyFunction([tblMember]![String_Value]),0) AS Sort2, IIf(Not IsNumeric([tblMember]!...............................

Can anyone help?

 

[Rich]

Post the Function and does it appear in the list of available function in your db?

 

[Parker]

Post the Function and does it appear in the list of available function in your db?

Public Function Pad(strIn As String) As String

        Pad = Right(String(9, " ") & strIn, 9)

End Function

Yes it does appear in the list of functions (and yes 'MyFunction' was a substitute name )

Later in the SQL I call another function (that is also listed as available). I am assuming that I may have the same problem with that as well but I don't know yet as it falls flat at the first call.

It is the first time I've tried to use a UDF in anything with Access so I must be doing sommat wrong. And it all came about because I picked up a thread and thought "I know how to do that --- I'll work the code and post it"

My theory will work just the practice is lacking

 

[Pat Hartman]

Is the function in a standard module or a class module? It needs to be in a standard module.

Also
SELECT tblMember.String_Value, IsNumeric([tblMember]![String_Value]) AS Sort1, IIf(IsNumeric([tblMember]![String_Value]),MyFunction([tblMember]![String_Value]),0) AS Sort2, IIf(Not IsNumeric([tblMember]!..................................

You don't need to repeat the opposite test in your IIf(). The IIf() syntax is -
IIf(condition, true path, false path) Therefore if the string value is numeric, it will follow the true path and if it is not numeric, it will follow the false path. There is no need to repeat the condition as a negative. Obviously if something fails the first test it will pass the second unless of course if it is null in which case it will fail both tests.

 

[Parker]

Is the function in a standard module or a class module? It needs to be in a standard module.

Also
SELECT tblMember.String_Value, IsNumeric([tblMember]![String_Value]) AS Sort1, IIf(IsNumeric([tblMember]![String_Value]),MyFunction([tblMember]![String_Value]),0) AS Sort2, IIf(Not IsNumeric([tblMember]!..................................

You don't need to repeat the opposite test in your IIf(). The IIf() syntax is -
IIf(condition, true path, false path) Therefore if the string value is numeric, it will follow the true path and if it is not numeric, it will follow the false path. There is no need to repeat the condition as a negative. Obviously if something fails the first test it will pass the second unless of course if it is null in which case it will fail both tests.

Thanks Pat

The function is in a standard module.

There is a reason for the reverse IIf statement------I need to capture the second part of an alpha numeric as a second string to carry out certain procedures with it. The SQL goes on to call a second function on the Alpha part of the string. Or at least it might if I can only work out how to call a function from the query in the first place.

So really what looks like a reverse IIf is nothing more than a second call to another function.

 

[Parker]

Bump

I'm gonna bump this because I still havn't resolved it and now it's driving me nuts.

I think I've followed all the right rules but I still can't call this or any other UDF from anywhere -- Query, form or macro.

I get the same message all the time "Undefined Function 'function name' in Expression".

What am I doing wrong?

 

[Pat Hartman]

Do you have a references issue? Search here for missing references.

 

[Parker]

Do you have a references issue? Search here for missing references.

Thanks Pat

Did you mean to give me a link on that or is it just to search this forum?

 

[Rich]

I quickly copied your function and tried it in a query, I had no problem with running it. Are you sure you've named it correctly in the query?

 

[Parker]

I quickly copied your function and tried it in a query, I had no problem with running it. Are you sure you've named it correctly in the query?

Thanks Rich

Well it's nice to know it works. I called it Pad ----- I can think of a few other names but not appropriate on here me thinks

Pat suggested missing references which is also a thought I had but I cant think that it would be that 'cause I have downloaded a few examples from other people that include UDF's and they seem to run OK

 

[Pat Hartman]

The thing about the "missing references" issue is that the error normally has nothing to do with the library that is actually missing. Some error is thrown when the first function of ANY tipe is executed.

 

[Parker]

The thing about the "missing references" issue is that the error normally has nothing to do with the library that is actually missing. Some error is thrown when the first function of ANY tipe is executed.

Still don't understand why it would run others but not mine though

 

[Rich]

I've had problems with Right/Left and Mid functions before, try unchecking one of your existing references

 

[explomikey]

Naming the Module

I ran into the same problem, and noticed that I had named the function and the module the same name.

When I changed the module name (I decided to use the default "Module1") all of a sudden the function started working in the query...