MS ACCESS 2007 Function not found

I wrote a Function in MS ACCESS 2007 using module ,please find the function below
Public Function SplitF(str1 As String)
Dim len1 As Integer
 For i = 1 To Len(str1)
    If Mid(str1, i, 1) = "F" And IsNumeric(Mid(str1, i + 1, 1)) = True Then
    len1 = Len(Mid(str1, i, InStrRev(str1, " "))) - Len(Replace(Mid(str1, i, InStrRev(str1, " ")), " ", ""))
    If (len1 = 0) Then
    SplitF= Mid(str1, i, InStrRev(str1, " "))
    Else
    SplitF = Mid(Mid(str1, i, InStrRev(str1, " ")), 1, InStr(Mid(str1, i, InStrRev(str1, " ")), " "))
   
    End If
   
    End If
   
  Next

End Function

MY QUERY is, when i'm calling from under Query , its showing the Function name is not found, When i'm using MS Access 2003 its working fine. i reduced the macro security levels in the Access , but still getting error. please show me where i'm doing the mistake.thx
LVL 2
lankapalaAsked:
Who is Participating?
 
BitsqueezerConnect With a Mentor Commented:
Hi,

this is not a problem of Access 2007 in general. I tested your function in a query with Access 2007 and it worked without a problem.

But maybe there are other sources of problems with your code or the rest of the code in your application.

Naming
I saw in another application where someone named a public variable "mID" (for "Member ID") and I wondered why the VBA "Mid" function always changes it's case to "mID" in the code until I found that variable: Please make sure that you never use any such name in any case where you can enter a name, in form names, control names, report names, variable names and so on. It is always a good idea to begin any name with a prefix like (in this example) "lngMID" so it can never conflict with any reserved name. The most often error is using the name "Date" for date fields for example (I had that in another application, hard to find as it was the name of a control in a form...)
Variable Declaration
You should ALWAYS use the line "Option Explicit" at the beginning of any module, class module, form and report code! This forces you to explicitly declare any variable so you must always think about the right variable type to use. If you don't, any variable will have the type "variant" which is on one hand slower than an explicit type and allows NULL values which can cause NULL errors in some places (for example, if you use a fieldname in your SplitF SELECT which allows NULL values and the query tries to use the value in your function it would cause an error in this case as here you explicitly declared "str1" as string type).
Function Return Value
The same as for declaring variables: You should always declare the right return value in the definition of a function. If you leave that out, it also will be variant. If you really NEED a variant return value, also declare the function with "As Variant" so you can directly read it in the code.
In case of this function it is not needed so you can declare the return value of the function with "As String"
Execution Time
To find out what your function is good for I experimented a little bit (as your code makes it very hard to read due to the big number of function calls in one line). As far as I saw it extracts a number from a string beginning with "F" to the next space. Some tips to make it faster (as you should always consider to speed up functions which should be used in SQL to not slow down the execution time of the SQL command too much):



The old one (using correct declaration saved 2 seconds in the test function below (21 seconds in 1,000,000 executions against 23 seconds before)):
Public Function SplitFOld(str1 As String) As String Dim len1 As Integer Dim i As Long For i = 1 To Len(str1) If Mid(str1, i, 1) = "F" And IsNumeric(mID(str1, i + 1, 1)) = True Then len1 = Len(Mid(str1, i, InStrRev(str1, " "))) - Len(Replace(Mid(str1, i, InStrRev(str1, " ")), " ", "")) If (len1 = 0) Then SplitFOld = Mid(str1, i, InStrRev(str1, " ")) Else SplitFOld = Mid(Mid(str1, i, InStrRev(str1, " ")), 1, InStr(Mid(str1, i, InStrRev(str1, " ")), " ")) End If End If Next End Function
Save results in variables if you need them later:
In this version the function result of Mid(str1, i, InStrRev(str1, " ")) is saved in a result string "strF" so it can be used in any further place where you need the same value again. This saves a lot of code running internally to execute the functions Mid and InstrRev (which will be called in any loop cycle).
Additionally the "Exit For" breaks the loop when a result was found - your old function runs the complete string which costs more time.
Result against the function above: Only 13 seconds execution time now:
Public Function SplitF(str1 As String) As String Dim len1 As Integer Dim i As Long Dim strF As String SplitF = "" For i = 1 To Len(str1) If Mid(str1, i, 1) = "F" And IsNumeric(Mid(str1, i + 1, 1)) Then strF = Mid(str1, i, InStrRev(str1, " ")) len1 = Len(strF) - Len(Replace(strF, " ", "")) If len1 = 0 Then SplitF = strF Exit For Else SplitF = Mid(strF, 1, InStr(strF, " ")) Exit For End If End If Next End Function
The IF statement:
This seems to decide between if the number is at the end or in the middle of the string. You can simply replace that if you add a space to "strF" (execution time now: 10 seconds):
Public Function SplitF2(str1 As String) As String Dim i As Long Dim strF As String SplitF2 = "" For i = 1 To Len(str1) If mID(str1, i, 1) = "F" And IsNumeric(mID(str1, i + 1, 1)) Then strF = mID(str1, i, InStrRev(str1, " ")) & " " SplitF2 = Trim(mID(strF, 1, InStr(strF, " "))) Exit For End If Next End Function
Why not use "Split"?
As the name of your function is "SplitF" it is a good idea to really use this very fast VBA function. In this version the string is split at any occuring space and only the result array is tested for the right value. Splitting the "IF" statement in two lines also results in more speed as only one condition will be tested in any loop cycle and only if an "F" is found the next condition will be tested. If you write it in one line always both conditions will be tested for. An "AND" can always be splitted in this way. (In VB.NET there is a new "AndAlso" which avoids the second test if the first result is false, but in VBA we do not have this choice).
Result now: 5 seconds execution time!
Public Function SplitF3(str1 As String) As String Dim str() As String Dim i As Long str = Split(str1, " ") For i = 0 To UBound(str) If Left(str(i), 1) = "F" Then If IsNumeric(Mid(str(i), 2, 1)) Then SplitF3 = Trim(str(i)) Exit For End If End If Next End Function

I tested the results with some test records in a table and all functions return the same value, only the speed is different. Moreover the new functions deletes the not needed spaces at the end of the string.

This is the function I used for speed testing of the above (results depends on the computer used and the applications currently running):

Public Sub testSplits()
    Dim d As Date
    Dim str As String
    Dim strDummy As String
    Dim i As Long
    str = "jhjshd F1233 1"
    
    d = Now
    For i = 1 To 1000000
        strDummy = SplitFOld(str)
    Next
    Debug.Print DateDiff("s", d, Now)
    
    d = Now
    For i = 1 To 1000000
        strDummy = SplitF(str)
    Next
    Debug.Print DateDiff("s", d, Now)

    d = Now
    For i = 1 To 1000000
        strDummy = SplitF2(str)
    Next
    Debug.Print DateDiff("s", d, Now)

    d = Now
    For i = 1 To 1000000
        strDummy = SplitF3(str)
    Next
    Debug.Print DateDiff("s", d, Now)

End Sub

Open in new window


But returning to your problem: I think the most likely reason that Access said "function not found" is a naming problem in your application.

Cheers,

Christian
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
First thing to check:

Open VBA editor ...
Tools>>References
Look for any shown as:

**Missing <SomeReferenceName>

mx
0
 
lankapalaAuthor Commented:
i checked i couldn't
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Can you post the SQL for the query using this Function ?

mx
0
 
GRayLCommented:
In the operation calling the function you need SplitF("myString")
0
 
lankapalaAuthor Commented:
yes ,The Problem is its working fine in the MS access 2003, so when i'm using MS Access 2007 its will showing error called "undefined Function "SplitF" in Expression.
Normaly i'm caling the function in Query-> New-. Design View -> in the Query writing Area i'm calling Function name like
SplitF([FieldName])
For example Fieldname is Faxnumber i'm calling
SplitF([Faxnumber])

its working fine in MS ACCESS 2003 , problem in MS ACCESS 2007.thx


0
 
als315Commented:
Look example. You function is working without any problems
db6.accdb
0
 
lankapalaAuthor Commented:
Wow Great , you are excellent .thx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
lankapala:
What was the actual resolution to this problem ?

mx
0
 
lankapalaAuthor Commented:
i used below function its worked fine. Mr.Bitsqueezer moderate my function

Public Function SplitF(str1 As String) As String
    Dim len1 As Integer
    Dim i As Long
    Dim strF As String

    SplitF = ""
    For i = 1 To Len(str1)
        If Mid(str1, i, 1) = "F" And IsNumeric(Mid(str1, i + 1, 1)) Then
            strF = Mid(str1, i, InStrRev(str1, " "))
            len1 = Len(strF) - Len(Replace(strF, " ", ""))
            If len1 = 0 Then
                SplitF = strF
                Exit For
            Else
                SplitF = Mid(strF, 1, InStr(strF, " "))
                Exit For
            End If
        End If
    Next
End Function
 
0
 
BitsqueezerCommented:
Hi lankapala,

but that was only a recommendation for a better function - not the reason for the problem "function not found"...;-)

Cheers,

Christian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.