MS Access Functions

MS Access Functions : MS Access functions has many built-in functions.

This reference contains the string, numeric, date, and information functions in MS Access.

MS Access String Functions

Function Description
Asc Returns the number code that represents the specific character
Chr Returns the character based on the number code
Concat with & Concatenates multiple strings together into a single string with the & operator
CurDir Returns the current path
Format Formats a string expression
InStr Returns the position of the first occurrence of a string in another string
InstrRev Returns the position of the first occurrence of a string in another string, starting from the end of the string
LCase Converts a string to lower-case
Left Extracts a substring from a string (starting from left)
Len Returns the length of the specified string
LTrim Removes leading spaces from a string
Mid Extracts a substring from a string (starting at any position)
Replace Replaces a sequence of characters in a string with another set of characters (a number of times)
Right Extracts a substring from a string (starting from right)
RTrim Removes trailing spaces from a string
Space Returns a string with a specified number of spaces
Split Splits a string into substrings (based on a delimiter) and returns the result as an array of substrings
Str Returns a string representation of a number
StrComp Returns an integer value representing the result of a string comparison
StrConv Returns a converted string
StrReverse Returns a string whose characters are in reverse order
Trim Removes leading and trailing spaces from a string
UCase Converts a string to upper-case

 

MS Access Numeric Functions

Function Description
Abs Returns the absolute value of a number
Atn Returns the arctangent of a number
Avg Returns the average value of an expression
Cos Returns the cosine of an angle
Count Returns the number of records in a select query
Exp Returns e raised to the nth power
Fix Returns the integer portion of a number
Format Takes a numeric expression and returns it as a formatted string
Int Returns the integer portion of a number
Max Returns the maximum value of an expression
Min Returns the minimum value of an expression
Randomize Allows you to change the seed value used by the random number generator for the Rnd() function
Rnd Generates a random number
Round Returns a number rounded to a certain number of decimal places
Sgn Returns the sign of a number
Sqr Returns the square root of a number
Sum Returns the summed value of an expression
Val Accepts a string as input and returns the numbers found in that string

MS Access Date Functions

Function Description
Date Returns the current system date
DateAdd Returns a date after a time/date interval has been added
DateDiff Returns the difference between two date values
DatePart Returns a specified part of a date
DateSerial Returns a date given a year, month, and day value
DateValue Converts a string to a date
Day Returns the day of the month (from 1 to 31) for a date
Format Takes a date expression and returns it as a formatted string
Hour Returns the hour (from 0 to 23) for a time value
Minute Returns the minute of the hour (from 0 to 59) for a time value
Month Returns the month (from 1 to 12) for a date
MonthName Returns the monthname given a number from 1 to 12
Now Returns the current system date and time
Second Returns the second of the minute (from 0 to 59) for a time value
Time Returns the current system time
TimeSerial Returns a time given an hour, minute, and second value
TimeValue Converts a string to a time
Weekday Returns a number (from 1 to 7) representing the day of the week for a date
WeekdayName Returns the weekday name given a number from 1 to 7
Year Returns the year (four digits) for a date

MS Access Information Functions

Function Description
CurrentUser Returns the name of the current Access user
Environ Returns the value of an operating system environment variable
IsDate Returns TRUE if the expression is a valid date, otherwise FALSE
IsNull Returns TRUE if the expression is a null value, otherwise FALSE
IsNumeric Returns TRUE if the expression is a valid number, otherwise FALSE