Summary -

In this topic, we described about the MS Access Functions Reference with detailed example.

MS Access Functions has many built-in functions like string, numeric, date, and advanced functions that are listed as shown in below –

MS Access String Functions are listed as follows –

Function Description Syntax
Asc ( ) It returns ASCII value for the specified character. Asc( character)
Chr ( ) It returns the character value for the specified ASCII value. Chr (ASCII value)
Concat with & By using & operator we can add two or more strings together. string1 & string2 & string3,..
Curdir It returns the full path of specified drive. Curdir(driver)
Format ( ) It Formats date or time or numeric values into required format. Format(value, Format)
Instr ( ) It returns the position of first occurrence of string in another string. Inst(start, string1, string2, compare)
InstrRev ( ) It returns the position of first occurrence of string in another string and it searches from end of the string. InstRev(string1, string2, start, compare)
Lcase ( ) It returns specified string in lower case in result set. Lcase (text)
Left ( ) It returns the specified extracted characters in result set. It extracts from left of the string. Left (expression, no_of_chars)
Len ( ) It returns the length of the specified string. Len (expression)
Mid ( ) It returns extracted characters from string starting at any position. Mid(string, start, length )
Ltrim ( ) It trims the starting spaces on string and returned it in result set. Ltrim (expression)
Replace ( ) It replaces the substring in a string with another new sub string with specified number of times. REPLACE (string1, find, replacement, start, count, compare )
Right( ) It returns the specified extracted characters in result set. It extracts from right of the string. Right(expression, no_of_chars)
Rtrim ( ) It trims the ending spaces on string and returned it in result set. Rtrim(expression)
Space( ) It returns the string in result set with specified number of spaces Space (number)
Split( ) It splits a string into an array of strings. Split(string, separator, limit, compare)
Str( ) It returns the numerical value into string format. Str(number)
StrComp ( ) It returns an integer value by comparing two strings. StrComp(string1, string2, compare)
StrConv ( ) It returns a converted string. StrConv(string1, conversion, LCID)
StrReverse( ) It returns a reversed string. StrReverse(String )
Trim( ) It delete the starting and ending spaces present in the string. TRIM (string)
Ucase ( ) It returns the specified expression in upper case in result set. Ucase (expression)

MS Access numeric Functions are listed as follows –

Function Description Syntax
Abs ( ) This function returns absolute value of a number. Abs (number);
Avg ( ) It returns average value for the specified expression. Avg(expression)
ACOS () It returns specified cosine value into radians i.e. angle in radians. ACOS (float_expression );
ASIN ( ) It returns specified sine value in to radians i.e. angle in radians. ASIN (float_expression );
Atn ( ) It returns specified tangent value in radians i.e. angle in radians. Atn(number);
ATN2 ( ) It returns specified two tangent values in radians i.e. angle in radians. ATN2 ( float_expression , float_expression );
Cos ( ) It returns cosine value for the specified angle in radians in specified expression. Cos (number);
Count ( ) It returns the number of records in SELECT statement. Count (expression )
Exp ( ) It returns the exponential value for the specified number Exp (number);
Fix ( ) It returns the integer part of the specified number. Fix(number)
Format ( ) It Formats a numeric value with specified format. Format(value, format)
Int ( ) It returns integer part for the specified number. Int( number)
Max ( ) It returns a maximum value from list of values. Max(expression)
Min ( ) It returns a minimum value from list of values. Min(expression)
Randomize() This function returns a random number in result set. Randomize (Seed);
Rnd ( ) It returns a random number. Rnd (seed);
Round ( ) It returns the nearest round value for the specified decimal values. Round (expression, decimal_values);
Sgn ( ) This function returns +1 if the specified value is positive. returns -1 if the specified value is negative. returns 0 if the specified value is zero. Sgn (number );
Sqr ( ) It returns the square root value of specified number. Sqr (number);
Sum ( ) It returns the sum value for the specified expression set of values. Sum(expression)
Val ( ) It returns the numeric value from the sting. Val(string)

MS Access date Functions are listed as follows –

Function Description Syntax
Date It returns the current system date. Date ( )
Dateadd It returns modified date by adding date/time interval to specified date. Dateadd (interval, number, date)
DateDiff It returns the difference between two specified dates DateDiff(datepart, date1, date2, firstdayofweek, firstweekofyear)
DatePart It returns a specified part of date as an integer. DatePart(datepart, date, firstdayofweek, firstweekofyear)
DateSerial It returns date from specified parts. DateSerial(year, month, day)
DateValue It returns date depends on string. DateValue(string_date)
Day It returns a day of month for specified date. Day(date)
Format It formats a date value with specified format. Format(value, format, firstdayofweek, firstweekofyear)
Hour It returns hour part from specified time/ datetime value. Hour(time/datetime)
Minute It returns minute part from specified time/ datetime value. Minute (time/datetime)
Month It returns month part from specified date. Month(date)
MonthName It returns an integer value which specifies month value. MonthName(number, abbreviate)
Now It returns current date and time on system date and time. Now ( )
Second It returns the seconds part from specified datetime and time value. Second(datetime/time)
Time It returns the current system time in result set. Time( )
TimeSerial It returns the time from specified parts. TimeSerial ( hour, minute, second )
TimeValue It returns time value based on string. TimeValue(String)
Weekday It returns an integer value that refers to week day of week for specified date. Weekday(date, firstdayofweek)
WeekdayName It returns Weekday name based on specified date. WeekdayName(number, abbreviate, firstdayofweek)
Year It returns the year part from specified date. Year(date)

MS Access advanced Functions are listed as follows –

Function Description Syntax
CurrentUser It returns the name of the current database user. CurrentUser( )
Environ It returns a string in result set which contains operating system environment variable. Environ(number/string)
IsDate It returns true or false in result set by checking specified date is valid or not. IsDate(expression)
IsNull It returns true or false in result set by checking specified expression has NULL value or not. IsNull( expression)
IsNumeric It returns true or false in result set by checking specified expression has Numeric value or not. IsNumeric(expression)