use of functions in access queries

One of the basic principles of relational database design is that you don't store calculated fields in tables.

For example, you never store a person's age. It should be obvious why not - because it changes all the time, well, every year, anyway. You store the date of birth and whenever you need the age, you calculate it.

It's the same with invoice amounts, sales totals, commission calculations, etc.

Calculations can be done in the reports you print or in the forms you display on the screen. Very often they are done in the query that is used as the source for the form or the report. Because a query, unlike a table, is dynamic, the calculations are re-done every time the query is called.







a short note about date formats

There are different ways of writing dates all over the world. If you're American you write dates as mm/dd/yy, like 1/2/03 which you would read as January 2nd, 2003. But someone in Europe would read that as: February 1st, 2003 because their date format is dd-mm-yyyy.

It can get confusing. So, for most of the tutorials I'll use the international date format (ANSI format) which is: yyyy-mm-dd. Note that it uses the hyphen instead of the slash as a separator.

Fortunately, Access is very flexible when it comes to date formats. It will recognize March 18, 2009 or 18 Mar 09 or 2009-03-18 as valid dates and will convert them all to the internal date format in your system. And it is smart enough to know that 02/29/2011 is not a valid date.

the age() function

There is no built-in age function in Access. You have to build your own, or get one from somewhere, like here.

You can't just subtract year of birth from year of today to get the age. Someone with a date of birth of 2000-12-15 wil be 10 years old until 2011-12-15.

Here's an example of the simplest age calculation (you can copy/paste this if you need it):

Age: DateDiff("yyyy",[date of birth],Now())+(DateSerial(Year(Now()),Month([date of birth]),Day([date of birth]))>Now())

You could use that directly in a query where you have a column called [Date of birth], like this:



This will work but it's not really practical because you have to copy and paste the code every time you need to do the calculation in a query or a form or a report. It gets tedious.

The best way is to create a function in a module. The function has the advantage that it is available to every object in the application. In any form or report you only have to call the function, pass it the DOB parameter and it will return the age.

First, you create a new Module.

Then, you write the VBA code to create the function. At this time you probably don't have the programming knowledge to write code so, you just have to copy and paste the code.

In the GarageMike app there already is a module called AgeCalc that has the simple age function. What you can do for practice is create a more elaborate function. It will validate that the parameters you give it are valid dates and it can calculate the age as of a date other tha today (today is the default if you don't specify another date). You can create it in the same module but you'll have to give it a different name (you can't have 2 different Age() functions).

Here's the code that you'll need for Age2():

Function Age2(varDOB As Variant, Optional varAsOf As Variant) As Variant
    'Provided by Allen Browne
    'Purpose:   Return the Age in years.
    'Arguments: varDOB = Date Of Birth
    '           varAsOf = the date to calculate the age at,
    '                                or today if missing.
    'Return:    Whole number of years.
    'Note the use of Age2, the function name, in the code.
    
    Dim dtDOB As Date
    Dim dtAsOf As Date
    Dim dtBDay As Date  'Birthday in the year of calculation.

    Age2 = Null          'Initialize to Null

    'Validate parameters - must be valid dates
    If IsDate(varDOB) Then
        dtDOB = varDOB

        If Not IsDate(varAsOf) Then  'Date to calculate age from.
            dtAsOf = Date
        Else
            dtAsOf = varAsOf
        End If

        'Date AsOf must be greater than Date Of Birth
        If dtAsOf >= dtDOB Then     
            dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
            Age2 = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
        End If
    End If
End Function


Now, if you have the AgeCalc module from GarageMike, just paste the code into the module.

age module


Then you can use one or the other of the functions in a query to display the age of customers:

age function


Once you've created a function in a module all other objects in the application can call that function whenever the same calculation or a similar one is required.

For example, Age() could be used with [Date hired] as the parameter to return seniority instead of age for employees.





the iif() function

In the Financial app, there are several instances where conditional calculations are required in queries.

The CPA amount, often calculated in queries, is different for different product types. It uses one formula for a "mutual fund" and another formula for other types.

Those calculations make extensive use of the IIF() function, one of the Access built-in function.

The IIF() function has 3 parameters. The syntax is this:
IIF(condition, calctrue, calcfalse)
If condition is true calctrue is executed else calcfalse is executed

In the SalesByRep query it is used like this:

iif function


Functions are not used exclusively in queries. In the Financial app the IIF() function is also used several times in the reports. In some cases it would appear in forms, although that is not common. Usually, forms are generated from queries and use calculations performed in the queries.

There will be more to say about functions later because we'll need some special functions for our forms and reports.






Top