Solved

MS ACCESS 2007 Function not found

Posted on 2010-11-12
11
669 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:lankapala
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
First thing to check:

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

**Missing <SomeReferenceName>

mx
0
 

Author Comment

by:lankapala
Comment Utility
i checked i couldn't
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Can you post the SQL for the query using this Function ?

mx
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
In the operation calling the function you need SplitF("myString")
0
 

Author Comment

by:lankapala
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:als315
Comment Utility
Look example. You function is working without any problems
db6.accdb
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:lankapala
Comment Utility
Wow Great , you are excellent .thx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
lankapala:
What was the actual resolution to this problem ?

mx
0
 

Author Comment

by:lankapala
Comment Utility
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
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi lankapala,

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

Cheers,

Christian
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now