MS Access Functions

Microsoft Office Access provides many built-in functions that make it simpler and more effective. There are different perform that can be used to perform different tasks in MS Access.

Let us discuss some of the functions provided in the application that can be directly used on the data. To make it easier to understand, we have divided the functions into several categories based on the parameters and their computations.

The categories in which the functions can be divided are as follows:

String Functions
Mathematical Functions
Date/Time Functions
Logical Functions
Group by Functions
Data Type Conversion
Domain Aggregate Function

There are two types of functions in MS Access:

These functions can be used in SQL Queries in the MS Access
These Functions can be used in the VBA applications.

Note: Some of the functions in MS Access can be used in both SQL Queries and VBA.

Below we have listed some of the MS Access functions and their tasks, dividing them into the categories mentioned above.

MS Access String Functions

Function	Description
Asc	It takes a single character as the input and returns the ASCII value of that character.
Chr	You can enter the ASCII value of the character in the function, and it will return the character for that code.
Concat with &	The function allows you to join several strings together into a single string.
CurDir	The function will return the complete path for a specific or current directive or drive.
Format	It is used to format a specific type value with a determined format in the string.
InStr	The function is used to check whether an element or string is present in the given string or not. It returns the index of the first occurrence of the element or string in the string.
InstrRev	The function performs a similar task as the Instr, but it searches from the other end of the string. It will return the index of the first occurrence of the string from the end.
LCase	The functions take string input and convert all the uppercase characters into lowercase. All the other characters remain unchanged.
Left	The function returns all the characters of the string beginning with left.
Len	It requires a single parameter, that is, a string. The function will return the length of the string.
LTrim	The trim function is used to remove the spaces from the string. It removes the leading spaces from the string.
RTrim	The function is used to remove the trailing spaces from the string.
Mid	It returns the specified number of characters from the middle of the string starting from a specific position.
Replace	The function is used to replace a part of the string with another string in the given string a specific number of times.
Right	It is used to return the characters starting from the right side.
Space	It is used to return the string with a specific number of space characters.
Split	It is used to divide the string into an array of substrings.
Str	It is used to convert the numerical data into a string data type.
StrComp	It is used to check whether two strings are equal or not. It allows the user to compare two strings.
StrReverse	It is used to return the reversed string.
Trim	The function is used to remove both trailing and leading spaces from the string.
UCase	The function converts all the lowercase characters of the string into uppercase characters. Rest all the characters remain unchanged.

MS Access Numeric Function

Function	Description
Abs	It returns the absolute value of the number. It returns the positive value of any number entered.
Atn	It takes a single digit as input and returns the arc tangent for the number entered.
Avg	It is used to calculate the average of an expression. The average is the sum of all elements in the expression divided by the total number of elements.
Cos	It is used to return the cosine value of the angle.
Count	It keeps track of records returned by a specific query. It returns the number of records returned by that specific query.
Exp	It computes the exponential of the number. The exponential is e raised to the power of a specific number.
Int	It returns the integer part of the number.
Max	The function is used to return the maximum value in the expression.
Min	The function is used to return the minimum value in the expression.
Randomize	It is used to initialize the generator with a seed value. The generator is used to generate a random value in the application.
Rnd	It is used to return a random value in the application.
Round	It takes a floating point number and returns the round to a specified decimal place.
Sgn	It returns the sign of a number. It returns the positive and negative signs of the number.
Val	It takes a string as an input. It will print all the numbers present in the string.

MS Access Date/Time Functions

Date	It will return the current date that is set in the system.
DateAdd	It is used to add a date or a time interval to a specific date or time. It will return the time or date after performing the addition.
DateDiff	It is used to compute the difference between two dates. It will return the result.
DatePart	It is used to return a specific part of the date.
DateSerial	It will return a specific part from the entered date. It can return the year, month, or day from the date.
DateValue	It takes a string as an input and will return the date from the entered string.
Day	It will return on the day that was on the mentioned date.
Format	It is used to represent the data in a specified format.
Hour	It will return the hour from the datetime mentioned.
Minute	It will return the minute from the datetime entered in the function.
MonthName	It will return the name of the function for a corresponding number entered.
Now	It will return the current date and time set in the system.
Second	It will return the second from the datetime.
Time	It will display the current time of the system.
TimeSerial	It will return the time serially.
TimeValue	It will return the time based on the string.
Weekday	This will give the weekday number for the entered date in the function.
WeekdayName	It will return the name for the weekday name based on a number.
Year	It will give the year value from the date entered.

Logical Functions in MS Access

And	The functions follow the same role as the & operator; it will return true only when all the conditions are true; if any of the conditions is false, it will return false.
Case	This code can only be executed in VBA. It works similarly to the IF-THEN-ELSE statement.
Choose	The function prints a particular value from a list of values using the position of the value.
First	It will return the first result from a given set of queries.
FOR…NEXT	This function is also limited to VBA, and the function is used to implement for loop in the application.
iif	It is used to check multiple conditions in MS Access. It will return one value if a condition is true, while it will return the other value if the other condition is true.
Last	It will return the last value returned by a set of queries.
Or	It works similarly to an operator. That is, the function will return if any of the conditions are true. If all the conditions are false, then it will return false.
Switch	It works similarly to a switch in any other programming language. It takes an expression, and the expression is evaluated, and based on the expression, it will check a list of conditions. The first condition returning true will be executed.
WHILE…WEND	It is used to implement the While loop in the VBA. This function is used when the user does not know the number of times you want to implement the VBA code in your application. The loop requires a condition and is executed only when the condition is true. It is possible that a while loop to not execute even once.

Information Function in MS Access

CurrentUser	It is used to return the Current User that is accessing the application. If the database the user is currently working on is secured, it will return the user that is currently logged in to your system. But if the database is not secured, the function will simply return Admin. Admin is the default user.
IsDate	The function is used to validate the date. It will only return true if the date is valid; else, it returns false.
IsNull	The function is used to check whether the value entered is null. It will return true if the value is null; else, it returns false.
IsNumeric	The function is used to check whether the value entered is numeric. It will return true if the value is numeric; else, it returns false.
Environ	The function is used to return the value of the environment variable of your Operating System.

Domain Functions in MS Access

DAvg	The function is used to compute the average of a specific set of numerical values stored in a particular domain. You can select a particular range of values for which you want to calculate the average.
DLast	The function returns the last value stored in the MS Access table or a specific domain.
DMin	The function returns the minimum value stored in the MS Access table or a specific domain. The user can also set criteria in the function. The criteria are similar to a WHERE clause in the SQL.
DSum	The function is used to compute the sum of a specific set of numerical values stored in a particular domain. You can select a particular range of values for which you want to calculate the sum.
DMax	The function returns the maximum value stored in the MS Access table or a specific domain. The user can also set criteria in the function. The criteria are similar to a WHERE clause in the SQL.
DCount	The function finds the total records in the MS Access Table. It returns the total number of records in the domain or the table.
DFirst	The function returns the first value stored in the MS Access table or a specific domain.
DLookup	The function returns a specific value from the table or a particular domain.

Data Type Conversion Function in MS Access

CBool	The function is used to convert the value entered into a boolean value.
CDbl	The function is used to convert the value entered into a double value.
CSng	The function converts the value entered into a single precision value.
CByte	The function is used to convert the value entered into a byte value. The value returned will be between 0-255.
CDec	The function converts the value entered into a decimal point value.
CStr	The function is used to convert the value entered into a string.
CCur	The function is used to convert the value entered into a currency.
CInt	The function is used to convert the value entered into an integer value.
CVar	The function is used to convert the value entered into a variant value.
CDate	The function is used to convert the value entered into a date. The expression taken as the input is converted into an equivalent date.
CLng	The function is used to convert the value entered into a long integer value.

Group By Function in MS Access

Avg	This function is different from the DAvg function as it is used to return the average or the arithmetic mean for a set of values that are returned by a selected query and not for a selected domain or table.
Max	This function is different from the DMax function as it is used to return a minimum value from a set of values that are returned by a selected query and not for a selected domain or table.
Sum	This function is different from the DSum function as it is used to return the arithmetic sum for a set of values that are returned by a selected query and not for a selected domain or table.
Count	This function is different from the DCount function as it is used to return the total number of records from a set of values that are returned by a selected query and not for a selected domain or table.
Min	This function is different from the DMin function as it is used to return the minimum value from the set of values that are returned by a selected query and not for a selected domain or table.

File/Directory Functions in MS Access

ChDir	The function is used to change the current directory or file in which operations are being performed.
Dir	The function will return the name of the file for the corresponding path and the mentioned attributes if the user wants to retrieve multiple filenames for the specified path or the attribute. Then the user can implement the function without any arguments.
GetAttr	The function returns an integer value. The value represents the attributes of a file, folder, or directory.
ChDrive	The function is used to change the current drive in the application.
FileDateTime	The function is used to return the date and time for the last modification performed in the file. Initially, it stores the date and time the file was created.
MkDir	The function is used to create a new folder or directory.
FileLen	It returns the size of the file whose path is specified in the function. The size of the function is represented in Bytes.
SetAttr	It is used to set an attribute for the file.