fbpx
Logo
Mail us
ben@accessdatabasetutorial.com
accessdatabasetutorial
Home » Functions » Microsoft Access String Function: Borrowing String Functions from Excel

Microsoft Access String Function: Borrowing String Functions from Excel

Microsoft Access String Function: Borrowing String Functions from Excel


I published an article last week on how you can use an Access string function that doesn’t really exist in Microsoft Access. In fact, you can build any function you like but using Microsoft Excel which has the function I wanted to utilise saved my having to create a function from scratch.Check it out below how you can do this…

access string function

Custom Microsoft Access Functions: How To Borrow Functions From The Microsoft Excel Library

The definition of any function no matter the application is the ability to calculate and process passing variables or values to return a single value (the answer).

All we do is simply call the function (by its name) and place some parameters (some are optional) and let the system reveal the answer by return. Where this is used within the context of Microsoft Access can be one of many places including tables, queries, forms and reports.

Custom Access database functions will either be a user writing VBA code compiling routines and other pre-defined Access functions to utilise a tailored function for these objects. Another way to build a custom function is to simply borrow from another application (if it exists) and don’t re-invent the wheel!

The simple steps to building an Access database function

So here’s how to do just that showing you the following simple steps in borrowing other functions in this case from Microsoft Excel.

The example I’m going to show you is to borrow Microsoft Excel’s Proper function which doesn’t exist in Microsoft Access. This function simply returns the initial capitalisation of a word i.e. “access database” to “Access Database“.

1. Create a new module which will take you into the Visual Basic Editor view.

2. From the menu bar, choose Tools and then References… to display the references screen and scroll down for “Microsoft Excel X.0 Object Library” (where X.0 represents your version and in my case 14.0 = version 2010).

3. Add the following VBA code:

Public Function Proper(field As String) As String
   Dim xlf As Excel.WorksheetFunction
   Set xlf = Excel.WorksheetFunction
   Proper = xlf.Proper(field)
End Function

4. Save the changes and you are good to go.

Now in a query, you can call this function which will be listed in the Expression Builder tool under the name of the database in the module name (i.e. Module1 if it were left unnamed).

When you wrap the Proper function around a field i.e. Proper([CompanyName]), it will convert words to initial capitalised letters.

Custom Microsoft Access functions are easy to build and use. See which other Excel functions could serve you well.

Another Tip for you! You may want to build a separate module (give it a name i.e. MyFunctions) and export the module into a ‘bas’ file which can then be distributed and imported to other Microsoft Access databases. Don’t forget to reference the Excel library for each database.

I invite you to keep up to date with my articles and eBooks which covers a lot of details and can be found at http://AccessDatabaseTutorial.com.

From Ben Beitler – “Your Access Database Expert”

Article Source: http://EzineArticles.com/?expert=Ben_S_Beitler

Microsoft Access String Function: Borrowing String Functions from Excel

The above example is a basic (perhaps crude) illustration of what can be done and this Access string function is a handy one indeed (well, I think so!).

The only caveat I have however is that it may seem a little slower to execute compared to a built in Access string function as it will need to load the Excel object reference for each new session of Access.

I do provide one on one online coaching in programming with Microsoft Access VBA. Check out my online coaching offer here.

Tags: , , , , ,

2 Responses so far.

  1. I built a library of VBA functions into a module which I then stored as an exported (bas) file so that for every new Access database project, I simply imported this file which then makes this ready to be used as if it were part of Microsoft Access!

  2. I’m extremely impressed along with your writing skills as smartly as with the layout on your weblog. Is this a paid theme or did you modify it yourself? Anyway keep up the nice high quality writing, it is uncommon to look a nice blog like this one these days..

Leave a Reply

Your email address will not be published. Required fields are marked *