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

How Big is Your Cubit? – Conversion Functions Round 2

Last Month we created a conversion function that converted cubits to inches. That function, though interesting, was limited in that it converted cubits to inches only for someone whose cubit measurement (elbow to fingertip length) happened to be 30 inches long.

This month we'll make our function much more flexible in that it will be able to convert cubits to inches for any factors (or forearm lengths) defined in our database.

In this issue
  • Tip of the Month – Adding Totals to Reports
  • The Foundation
  • Testing
  • Limitations and a Look Ahead
  • Trap of the Month – Don't use Queries as the Source for Reports or Forms

  • The Foundation

    Last month our function contained the following code (if you need to review check out August 06):

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

    The value 30 is our conversion factor, and it works wonderfully for me since it represents the length of my cubit. What about Andre the Giant who has a cubit of 54 inches, or Mildred the Midget who has a cubit length of 12.5 inches? We could hard code separate functions for those varying lengths, but wouldn't life be easier if we could just tweak our function to handle cubits of different lengths? That's just what we'll do! And to make life even easier, once we've entered the length of a person's cubit, we never want to worry about it again. This situation calls for a cubit table – like the following.

    Our table has three fields

    1. An AutoNumber primary key called CubitKey – I'm a fanatic about primary keys; see Keys are Critical.
    2. A Person field to record the person's name
    3. A cubit length (CubitLen) field where we'll record the length of the cubit for each person.

    Once we populate the table it looks like this:

    Now we can tweak our function code to handle variable cubit lengths for individuals from the cubit length table.

    Our revised function looks like this

    Function CubitsToInches(dblCubits As Double, strPerson As String) As Double
       Dim dblInchesPerCubit As Double
       dblInchesPerCubit = DLookup
    ("CubitLen", "tblCubit", "Person = '" & strPerson & "'") CubitsToInches = dblInchesPerCubit * dblCubits
    End Function

    Compared to our earlier function we've made just a few changes:

    1. We've added another argument (see last month's issue for an explanation of arguments) strPerson As String to the function. This code allows a user to select a person and associated cubit length to be used for the conversion. We're asking that this value comes in as a text string, hence the argument As String
    2. We've added a new variable Dim dblInchesPerCubit As Double. This variable will contain the conversion factor (the inches per cubit for each individual). For example, Andre’s conversion value (inches per cubit) is 54; for Mildred it will be 12.5.
    3. We've added a line to lookup the value of inches for the person requested: dblInchesPerCubit = DLookup("CubitLen", "tblCubit", "Person = '" & strPerson & "'")

    This lookup function is a powerful way of easily getting a value from a table. It's part of a much larger discussion of domain functions, so I won't go into it too deeply here, but basically it takes three arguments

    • the field to lookup, in our case CubitLen
    • the name of the table tblCubit and
    • the criteria: "Person = '" & strPerson & "'"

    The criteria is the only tricky part, so I'll break that down for you. It says give me the record where the field "person" is equal to the variable strPerson. In other words, if the conversion is to be done for Mildred, go to the record where the field Person is equal to "Mildred."

    We also have some punctuation to deal with. Each of the three arguments: Field Name, Table Name, and Criteria are surrounded by double quotes, and our criteria field Person is a text field so that the person we are looking for must also be in quotes, but because the criteria field as a whole is in double quotes, when we designate the person we use single quotes. Finally since we're using a variable strPerson we also are doing concatenation – hence our use of the ampersand.

    I recognize that this is not terribly friendly or easy – many users have trouble with dlookup statements and I've not fully covered them here. I'll be doing that in a future issue. For your purposes in the mean time when you use a dlookup statement use the Access Help system to get an explanation. Though the help system is not terribly good, it will give you a start.


    Now that we have our function in place, let's give it a whirl.

    In the VBA immediate window (type ctrl-G to get there) type:

    ? CubitsToInches(3,"Andre")
    and hit return.

    This statement is used to answer the question “How many inches long are 3 Andre cubits?” It gets the answer by calling Andre’s conversion factor from the table. You should get back 162. That’s 3 times 54, the Cubit measure for Andre.

    Now try the same thing replacing Andre with Mildred and you'll get 37.6.

    Our function is considerable more flexible now. It can return the appropriate cubits for anyone who is recorded in the Cubits table.

    Limitations and a Look Ahead

    Our function is pretty nifty and has many uses – it can be called in a form, report or query – we'll see that and more as we move forward. But what happens if a user asks for inches to cubits for someone who is not in our table? That's a real problem and currently would cause a very ugly error message – which we really want to avoid. We'll also tackle that problem as we make our cubits to inches function more powerful and flexible.

    Trap of the Month – Don't use Queries as the Source for Reports or Forms

    This is a trap that I see a lot of users fall into, and Access makes it very easy to be a victim here. When you're creating a form or a report, Access not only invites you to use a query as a data source, but if you build the data source from within the form and explicitly save the query, you'll end up creating a new query.

    The problem here is two fold – as your application develops you'll have more and more queries and it will become much harder to navigate. Although this is a problem, it’s not nearly as serious as the one you face if you delete or change a query that a report or form uses. In that case you'll get an annoying dialog box asking you to supply cryptic information, and your form or report may not work at all.

    The way around this is to create the data source from the form or report design view (it's just like creating a query) and then close the view. Access will ask you “Do you want to save the changes made to the SQL statement and update the property?”

    Say yes and the SQL statement will be embedded within your report or Form. This process may not be intuitive so I'll cover it in much greater depth in a future article.

    Tip of the Month – Adding Totals to Reports

    I was at a customer site recently (I love all my wonderful customers!) and ran into someone who I had not met before who is a reader of the Wizard. After the meeting she sent me this email:

    My request for a topic I would like to see you cover in a future newsletter would be “How to count the number of records in a group or a report”. It would really be a big help to me if I were able to understand how that works. I always look forward to receiving the Access Wizard Newsletter, and appreciate your consideration of this request. Also, thank you for making this newsletter available!!!


    Thanks Sheila, I really appreciate the kind words.

    In Excel this task would be trivial, and that’s the tool Sheila uses as a work-around (she copies her data to Excel and counts the records there). However it's not too tough in Access if you know the trick.

    Let's say that you have a field on the report called "Customers." To show the total number of customers for the report, use the report footer section. Go to the design view; if the footer is not visible, choose View, then choose View Header/Footer to go to the footer section. Add a text box to the footer (choose View Toolbox if the Toolbox is not visible, then click on the textbox tool) and place a textbox wherever you would like the total to appear.

    In the textbox type “=Count([Customers])”, omitting the quotes. Now run your report and you should see a count of the customers.

    If on the other hand, if you wanted to add up all the units that the customers have ordered, just change the word “Count” to “Sum” and include the field name for the units, e.g. =Sum([Units])

    Once you know this trick you won't have to resort to Excel - you may simply embed the counts in your report!

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!