MS Access Functions

Here is a list of the most commonly used functions in Access. This is alphabetical listing of pointers to those articles which explain these functions in more detail.

Sponsored Links:

Related Topics

MYSQL functions within Access
As we know UUID() and NOW() functions are MySQL functions. I like to set equivalent functions and features in Microsoft Access.

What will be the same functions and where to put?

Built-In Functions Fundamentals
Once again, since Microsoft Access doesn't inherently provide a programming environment, it relies on logical functions to take care of this aspect. The Choose() function is one of those that can test a condition and provide alternatives. The Choose() function works like nested conditions. It tests for a condition and provides different outcomes depending on the result of the test.
Learn: The Switch Function, Arithmetic Functions, String Functions, Date and Time, Series-Based Functions, Domain-Based Functions, Business Functions and Finance Functions.

Queries-Based Functions
These functions can also be used in queries and even included in SQL statements. The SQL interpreter of Microsoft Access can recognize these functions as long as you use them appropriately.
Topics: Queries and Built-In Functions, Summary Queries and SQL Aggregate Functions.

Use Access 2007 Aggregate Functions
The Access 2007 aggregate functions calculate values over a range of data. The totals row is new with Access 2007 and makes it easier to use aggregate functions such as averages, counts, sums and totals. You may still use these functions in queries and Visual Basic Applications as before but the "Totals" row is generally faster. The following tutorial will show how the use the Access 2007 aggregate functions.

Introduction to Built-In Functions
Before creating a new function that perform a specific task, first find out if that function exists already. The library built in Microsoft Access is very large (one of the largest you will ever see in the industry) and provides functions that cover many subjects, including general topics, algebra, conversion, finance, accounting, date, time, and strings, etc. The available functions are highly reliable so you can safely use them.
Topics: Statistical Functions, Date and Time-Based Functions, The Win32 API.

Where can I get information on SQL syntax in Access?
I am experienced using SQL in Oracle but have found some difficulty writing reports in Access, not sure if I am making syntax errors or the functions I am using aren't not compliant with Access.

Was wondering if there were some good resources you guys could recommend with Access SQL examples and possibly list of functions that do and do not work. Particularly having trouble withCASE statements, Dense Rank, and some aggregate functions.

Sorting and creating functions
I have recently purchased Access 2007 because Excel is limited to 1 or 2 million cells.

I am working with massive amounts of info.
In Excel, I can sort the table by customer ID and then add columns that will contain IF() functions to flag certain rows as needed for analysis.

Can I create IF() functions in Access?
If so, is the process to create a funcion different in Access than in Excel

Aggregate Functions in SQL and Microsoft Access
With SQL there are many functions available that allow you to perform or manipulate calculations on your data. These functions can be used as part of the SQL SELECT statement.

MS Access Built-in Functions
MS Access comes with lots of built-in functions. You can use the power of these functions while building a query.

How to: Use Aggregate Functions to Work with Values in Access SQL
Aggregate functions are used to calculate statistical and summary information from data in tables. Learn how to use aggregate functions in Access SQL.

MS Access 2010 DATE Functions
Access encompass variety of powerful tools, that facilitates user to use dates and date ranges to be specified in a certain criteria. To get by with the complex logic in your database that contains dates, then DATE functions could be very useful for you. This post will elaborate how to use simple DATE functions.

Conversion 2010 to 2003
I have made a fairly big Access 2010 database for a small community. Now they want me to convert it to Libre Office, but it only accepts mdb files (Access 2003 or 2000).

I got the message it could not convert because there are functions used which do not allow the conversion. I read about all the incompatible functions,

But still the message comes that it cannot convert because some functions still exist.

Functions and properties in Access 2007 blocked by sandbox mode
This article provides reference information about the functions and properties that sandbox mode allows and blocks in Microsoft Office Access 2007. Note that Office Access 2007 allows several new functions to run in sandbox mode, including MonthName and StrReverse, and it now blocks several more functions, including Assistance, ExportNavigationPane, and LoadCustomUI. As you proceed, remember that you must trust (enable the content in) the database or place the database in a trusted location for the information in this article to be of use.

Finance-Based Functions
Microsoft Excel provides a series of function destined to perform various types of financially related operations. These functions use common factors depending on the value that is being calculated. Many of these functions deal with investments or loan financing.

MinOfList() and MaxOfList() functions
Access does not have functions like Min() and Max() in Excel, for selecting the least/greatest value from a list. That makes sense in a relational database, because you store these values in a related table. So Access provides DMin() and DMax() for retrieving the smallest/largest value from the column in the related table.
Occasionally, you still need to pick the minimum or maximum value from a list. The functions below do that. They work with numeric fields, including currency and dates. They return Null if there was no numeric value in the list.

Function in Report is repeating itself?
I call some functions from a report but this report seems to have an issue; It runs all functions in correct order, twice.

Is there anyone who can tell me why it is doing this and how I can prevent it?

The functions are public and are being called from a common module.

Enhancing Queries
To enhance the result produced by a query, you can use some of the built-in functions of Microsoft Access. You can use a function to control the values that would display in the query or you can include the functions in the condition set to filter the values.
TOC: Using Functions, Summary Queries, Parameterized Queries, Action Queries: Table Creation, Action Queries: Appending Records.

Query in user defined functions (VBA)
I've been googling around to find some code examples of how to query Access from Excel but I found included the connection to the database. What I was thinking is to connect Excel to Access while opening the worksheet and then build my own functions without having to connect to the database again. Some like this:

Private Sub Workbook_Open()
'code to connect to the database
End Sub

Then, on the code modules i'll write my functions to get the data. If yes, I'm using Access and Excel 2007.

NAG functions
How to call NAG functions with Microsoft Access and Excel data.

Flexible Use of Functions
I use following functions to centrally change FYs. I use these functions in my queries.

Function getFromFY() As String
getFromFY = "FY 10"
End Function

Function getToFY() As String
getToFY = "FY 12"
End Function

But sometimes I have had to change the range. So does it mean that I need to create more functions or can I use these functions and use these so that I add or substract FY from an existing function. For example if I want to change the 'gettoFY' funmction by one more FY to FY 13, how could that be achieved in a query without having to create more functions