The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
August 2006

Conversion Functions 101 or How long is a Cubit?

And God said unto Noah "Thou shall build an Ark. This ark shall be 300 cubits long, 50 cubits wide and 30 cubits high."

Here in Massachusetts, May and June have been the wettest two consecutive months ever recorded; friends and neighbors were making jokes about getting ready for the great flood. We got more than 22 inches of rain. To give you some perspective, if that rain fell during our winter and was snow, we would have 22 plus feet of snow – 22 feet! We'd be digging out from here until Thanksgiving! It's been a wet spring.

The ark jokes were a bit of levity and humor during the rains, but I got to thinking about cubits. Just how long is a cubit? I asked a cousin of mine who is a biblical scholar and he said that a cubit was the distance from the tip of your fingers to the point of your elbow. Well – that sounds like an interesting but inconvenient system, as everyone's cubit would be a slightly different measurement. This month we begin tackling conversion functions – the processing to convert one unit of something to another unit of something; we'll use cubit conversion as our example. I promise that by the end of this series of conversion sessions, you'll have learned some useful information.

In this issue
  • Tip of the Month – How to Easily Insert the Current Date and Time
  • The Groundwork for Custom Conversion Functions
  • Analysis
  • How to Call a Function
  • Conclusion and a Look Forward
  • Trap of the Month – Avoid Parenthesis for Sub Routine Parameters

  • The Groundwork for Custom Conversion Functions

    Access has a host of built in conversion functions. You can get a list by opening Help and searching on the string "Conversion Functions". They tend to be fairly straightforward-the usual suspects: inches to feet, pounds to kilograms, etc, so I won't cover them here. Rather what we'll do is build our own simple custom conversion function, and from there we'll branch out to make things increasingly complex.

    The first thing we need to do is to create a new module. From the database window click on Modules and then click on New. This will bring you to a fresh module that will allow you to write code. Note that a module is a container for one or more functions and subroutines. Functions and subroutines are both VBA (Visual Basic for Applications) code. The major difference is that subroutines simply do work through the execution of code; functions also do work, however at the end of the process a function can return a value.

    If you do not see "Option Explicit" at the top of your new module, type it in as the first line (without the quotes). This line of code sets a requirement that all variables (something that can change) must be declared. What this offers is some protection against mistyping, which will make debugging (finding and solving errors in your code) easier. As a matter of fact, if Option Explicit doesn't show up automatically, you should change your settings so it does. From the code window choose Tools on the menu at the top of the screen, then choose Options. In the dialog box, click on the editor tab then select Require Variable declarations.

    Now that we’ve got the issue of declaring variables out of the way, let’s next type our simple conversion function as follows:

    Public Function CubitsToInches(dblCubits As Double) As Double
        CubitsToInches = 30 * dblCubits
    End Function


    This is not a very powerful or complex function as it stands. The real purpose here is to demonstrate how to create a function and then use it.

    Let's pull this function apart line by line:

    Public Function CubitsToInches(dblCubits As Double) As Double

    The first word public deals with something programmers call "scope." Scope is a concept that details from where a function or a subroutine may be called. There are two primary choices: public or private. Public indicates that the code can be called from anyplace within the application. A private function may only be accessed from within the form, report or module where it resides. Since we may be calling this cubit conversion function from any place within our application (who knows when we may want to convert from cubits to inches?), we declare this a public function.

    The next word is function. This indicates that the procedure will do some work AND return some value to the calling routine.

    CubitsToInches is the name of our function. There are a few things to note. First, the function name is all one word: no spaces, dashes, asterisks, exclamation points, piping characters, emoticons : ) or any other non-alphanumeric characters. Function (and subroutine) names may contain only letters and numbers. Also note that our name clearly represents what the function does – it changes cubits to inches.

    You don't have to use mixed case, but I recommend that you do because it makes both function and subroutine names easier to read. You may think that using mixed case in a function or subroutine name opens the door for errors while typing in the code, but VBA has a special feature that eliminates that problem. In VBA, when you type a function or subroutine name in other lines of code, you may type it in all lower (or upper) case (your choice). If you’ve typed the string correctly, VBA will automatically change the case to reflect the capitalization of the function or subroutine you’ve declared when you hit the enter key at the end of the line. This is a great feature that helps you to write consistent, error-free code.

    Next we have (dblCubits as double). The items within the parenthesis represent arguments (or pieces of data) that the function needs as input in order to do its work. The first item, dblCubits, is a variable name, and the second item, as double, defines the type of the input variable. In our case, the function expects to get input in the form of the number of cubits to be converted; this variable is called dblCubits. The as double represents the type of data we expect as the incoming cubits value. You’ll note that I included the type of data in the variable name by attaching the abbreviation dbl at the beginning of the variable name. Double represents a very precise floating point number that extends out many decimal places. As it is a very precise number, it requires a relatively large amount of memory to process, but memory tends to be cheap these days. As a rule, I tend to define data as double if I need a decimal number. We certainly didn't choose an integer (whole number) because we may want to process fractions, for example to find out how many inches are 2.5 cubits.

    This line of code closes with as double. This code defines the precision of the value returned by the function, which in this case is double. The next line is the one that does the work: CubitsToInches = dblCubits * 30

    In order for our function to return a value, we must define the operation: the name of our function, followed by an equal sign, followed by the calculation. The value of cubits to be converted is input and passed to the variable dblCubits, where it is stored. This value is then multiplied (the asterisk is VBA's representation of multiplication) by the conversion value of inches per cubit.

    So if we input 2, we'll get back 60. If we input 2.41, we’ll get back 72.3.

    Where did the 30 come from? I simply looked at my arm and guessed! Remember, this is only a basic demonstration. Don’t worry-we'll work with much more complicated (and practical!) functions in future issues.

    How to Call a Function

    We’ve created this wonderful function -- how do we use it? It’s easy to test the function in the code window where you’ve been creating the code; simply type control + g. By that I mean while holding down the control key, type the letter "g". Typing these keys will cause a new window, labeled “immediate” to open. Within that window, type the following:

    ? CubitsToInches(22)

    Then hit return. With this text, we’re asking the function to convert 22 cubits to inches. If everything goes according to plan, you'll see 660 on the next line. Time to declare victory!

    This demonstration of a trivial conversion function was a simple introduction to the power of functions. There are many ways to use them -- we'll investigate this as we move forward.

    Conclusion and a Look Forward

    Next month, we'll develop a more powerful and utilitarian function. We'll change it so that rather than using the length of my arm, the user will have the option of selecting another standard length (e.g. Andre the Giant’s arm or Mildred the Midget's arm). As a side note, please don't sic the PC police on me; that's the stage name she uses – wouldn't it be silly for Mildred to call herself Mildred the Vertically- Challenged?

    Trap of the Month – Avoid Parenthesis for Sub Routine Parameters

    This month we called a function and passed in parameters -- pieces of data that the function needs when it gets called. When we passed in these parameters some people call them arguments) we put them within parenthesis. You can see this in the testing example where we typed: ? CubitsToInches (22)

    Sub routines also take parameters, however when you pass in the parameters to a sub-routine you do not put them within parenthesis – another little inconsistency from Microsoft to keep us guessing.

    Tip of the Month – How to Easily Insert the Current Date and Time

    Access has two great keyboard shortcuts for inserting the current date and time. To paste the current date into a field, use Control + semicolon. Just hold down the Control Key and then press and release the ";" key.

    You can do the same thing to paste the current time – just use Control + Colon

    If you want both the date and the time do the Control semi colon, hit the space bar, and then do a Control colon.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886