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) |