Undefined function in expression

[sreyes27]

I hope someone helps me with this (I've been bugged by this one):


My environment consists of an Access 2003 database which contains some stored queries. In particular, the one I'm working with is called 'viewPatient' which contains some Visual Basic functions. The 'viewPatient' query works within Access fine. Unfortunately, I cannot call 'viewPatient' from Visual Basic 6.0 nor Visual C++ 6.0. I get the error "Undefined function 'GetPhone' in expression". viewPatient is a stored query which contains 'GetPhone' in it's select statement. Now 'GetPhone' is a Visual Basic public function (not a form) that is accessible wihin Access 2003 but not to an outside program like Visual Basic 6.0 nor Visual C++ 6.0. I thought the problem could be a 'sandbox' problem so I disabled it in the registry (value 0). No luck!!! I even tried to bring the public module 'GetPhone' into my Visual Basic project but no luck either. Can someone provide me help with this?

Thank you.
(I'm a newbie to VB)

 

[orna]

It might help to let evrybody look at the 'GetPhone' function

 

[sreyes27]

Reply (Undefined function in expression)

There's nothing spectacular about this function, But I'm enclosing it nevertheless. The jist to my e-mail has to do with problem getting to Visual Basic code by a stored query when stored query is called 'outside' of Access. That is by either c++ (Visual C++ Enterprise Edition 6.0 - - either Dao or Ado) or visual basic (Visual Basic Enterprise Edition 6.0). Any assistance would be greatly appreciated.

VB Function:

Public Function GetPhone(strPhoneNumber As String, Optional booSkipExt As Boolean)
'***************************************************************************
'Purpose: Used to display phone numbers
'Arguments: GetPhone(expC, expL)
' expC - Phone Number
' expL - Set to true if you wish to omit extension
'Returns: Character
'Created By: MicroFour, Inc.
'Created On: 15 Jan 99
'Comments:
'***************************************************************************

On Error GoTo GetPhone_Err

Dim strRetVal As String
Dim strArea As String
Dim strPrefix As String
Dim strPhone As String
Dim strExt As String

strPhoneNumber = Trim$(strPhoneNumber)

If isblank(strPhoneNumber) Then
GetPhone = ""
Exit Function
Else
strArea = Mid$(strPhoneNumber, 1, 3)
strPrefix = Mid$(strPhoneNumber, 4, 3)
strPhone = Mid$(strPhoneNumber, 7, 4)

strRetVal = Format(strArea & strPrefix & strPhone, "### ###-####")

'-- Determine is extension should be concatenated
If Not booSkipExt Then
strExt = Trim$(Mid$(strPhoneNumber, 11))
If Not isblank(strExt) Then
strRetVal = strRetVal & " Ext " & strExt
End If
End If
End If

If Trim$(UCase(strRetVal)) = "0" Then
strRetVal = ""
End If

GetPhone = strRetVal

GetPhone_Exit:
Exit Function

GetPhone_Err:
Select Case Err.Number
Case Else
Call m4GlobalErr(gstrObjectName, "GetPhone", Err.Number)
Resume GetPhone_Exit
Resume
End Select

End Function

 

[boblarson]

I would think it would be

IsBlank

because that is not a built-in function (as far as I can tell) and you would need to supply that one as well.

 

[sreyes27]

Thanks for your interest on this issue. Without getting into other issues that do not seem to be relevant, what you can do is just create a Visual Basic function and that is called by your stored procedure. Now call the stored query from a Visual Basic program. You will then see what I initially inquired upon. The Visual Basic program will report the error: Undefined function 'Your visual basic function' in expression. But within Access this error is not found. Or the fastest way to reproduce this error is the just remove most of the code in the original body of the 'GetPhone' call I pasted in the forum. The problem can then be easily seen. Please let me know what to do here. It seems that the best alternative is to re-code the Stored query to just retrieve the data and then pull the visual basic code into your visual basic program to do the processing of the code. I hope that makes sense.

Thanks for your help.

 

[boblarson]

Okay, I think I finally understand (correct me if not) -

Within Access you can call a function which is written in VBA within a Query. This functionality can be used with VB/VB.NET functions if they are compiled into a DLL which then is registered and a proper reference set to it within your Access program.

However, you mentioned a STORED PROCEDURE and that is a completely different puppy. It resides on SQL Server, not within Access, and therefore you can use User Defined Functions within SQL Server, but no VBA or VB functions. Now that being said, there is apparently ways to incorporate VB Script into Stored Procedures but I'm not familiar with that.

 

[sreyes27]

I apologize for the confusion, I meant to address stored queries within Access and not stored procedures. So the stored query calls or uses VB functions within Access without any issues. But outside Access I am having problems getting to the VB functions. I hope to have clarified this, if not please let me know.

 

[boblarson]

Have you compiled the function into a DLL and then registered it on your machine and set a reference to it from TOOLS > REFERENCES (you would also need this on each machine you are using it on)?

 

[sreyes27]

I'm sorry for being a newbie, can you tell me how I can do this?
Thank you!!!