Solved

MS ACCESS 2007 Function not found

Posted on 2010-11-12
11
683 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 75
ID: 34125471
First thing to check:

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

**Missing <SomeReferenceName>

mx
0
 

Author Comment

by:lankapala
ID: 34125496
i checked i couldn't
0
 
LVL 75
ID: 34125518
Can you post the SQL for the query using this Function ?

mx
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 44

Expert Comment

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

Author Comment

by:lankapala
ID: 34126873
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
 
LVL 40

Expert Comment

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

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 34127480
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
ID: 34132683
Wow Great , you are excellent .thx
0
 
LVL 75
ID: 34133521
lankapala:
What was the actual resolution to this problem ?

mx
0
 

Author Comment

by:lankapala
ID: 34134015
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
ID: 34134279
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display SQL maintenance plan SQL Code 3 43
T-SQL: Only Wanting One Record 8 56
Access Need to add combo box to sub form 10 44
Why Won't My Excel Workbook Refresh The Data 2 35
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 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