Microsoft Access Functions

There are over a 100 commonly available functions that can be utilised in an Access database application. I have no intention of listing them all here but the following category of functions may give you the appetite and confidence to explore other functions available.

? Arrays ? General
? Conversions ? Inspection
? Database ? Math
? Date/Time ? Messages
? Domain Aggregate ? Program Flow
? Error Handling ? SQL Aggregate
? Financial ? Text

The above categories can be found using the Expression Builder Tool (Keyboard shortcut: Ctrl + F2) and appears in the middle pane view (as shown below):

Access 2010 Function Expression Builder

The following three functions are typical examples of how you calculate in Access typically in a Query, Form or a Report:

IIf Function

Syntax is IIf ( condition, value_if_true, value_if_false )

Where the condition is the value that you want to test, value_if_true is the value that is returned if condition evaluates to TRUE and value_if_false is the value that is return if condition evaluates to FALSE.

An example could be to flag a narrative based on an [Invoice Date] field is either now ‘due‘ or still ‘current‘ based on the standard [Terms] of the client compared with the current Date function (another Access fucntion).

In a query, it would look something like:

Status : IIf ( ( [Invoice Date] + [Terms] ) < Date , “Due” , “Current” )

In a form or report textbox control, same would look like:

= IIf ( ( [Invoice Date] + [Terms] ) < Date , “Due” , “Current” )

DLookup Function

Syntax is DLookup ( FieldName , TableName , Criteria )

Where the the FieldName is a field, calculation, control on a form, or function that you wish to return, TableName is the set of records. This can be a table or a query name and Criteria is optional which serves as the WHERE clause to apply to the TableName.

An example would be used to locate the [Terms] for a client based on their [Customer ID] in the table [tbl_Customers].

In a query, it would look something like:

Terms (days) : DLookup ( “[Terms]” ,   “[tbl_Customers]” , “[Customer_ID] = 123″ )

This type of function is used for a non-relationship environment where there is no common link to the source table (or query).

DateAdd Function

Syntax is DateAdd ( interval, number, date )

Where the interval is the time/date interval that you wish to add, the number is the number of intervals that you wish to add and date is the date to which the interval should be added.

An example could be to add 3 months to the [Order Date] field using “m” which epresents the interval for month, 3 represents the number of months to add and [Order Date] is the field it’s calculating.

In a query, it would look something like:

Report Date: DateAdd ( “m” , 3 , [Order Date] )

Other interval codes:

yyyy (Year), q (Quarter), y (Day of the Year), d (Day), w (Weekday), ww (Week),
h (Hour), n (Minute) and s (Second).

There are many other functions to choose from and you can always use the Microsoft Access help files for more details.

You may be familiar with a few of the functions especially if you are an Excel user like Len, Trim, Left, Right, Round, Choose and others.

Finally, you may note that for VBA users (Excel and other applications), these are actually the same functions you use directly in VBA code.

This entry was posted in MS Access, Queries, Utilities, VBA and tagged , , , . Bookmark the permalink.

2 Responses to Microsoft Access Functions

  1. Ben says:

    Please expand on what you are trying to do.
    Is this in a query, form or report/?
    What are the names of the fields you are trying to calculate?

  2. santosh says:

    please sent me formula in access 2003 ex- 01-04-11 to 30-6-11=3 months

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.