MS Access Functions — SQL interactive lesson

MS Access has many built-in functions.

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

MS Access String Functions

Function	Description
Asc	Returns the ASCII value for the specific character
Chr	Returns the character for the specified ASCII number code
Concat with &	Adds two or more strings together
CurDir	Returns the full path for a specified drive
Format	Formats a value with the specified format
InStr	Gets the position of the first occurrence of a string in another
InstrRev	Gets the position of the first occurrence of a string in another, from the end of string
LCase	Converts a string to lower-case
Left	Extracts a number of characters from a string (starting from left)
Len	Returns the length of a string
LTrim	Removes leading spaces from a string
Mid	Extracts some characters from a string (starting at any position)
Replace	Replaces a substring within a string, with another substring, a specified number of times
Right	Extracts a number of characters from a string (starting from right)
RTrim	Removes trailing spaces from a string
Space	Returns a string of the specified number of space characters
Split	Splits a string into an array of substrings
Str	Returns a number as string
StrComp	Compares two strings
StrConv	Returns a converted string
StrReverse	Reverses a string and returns the result
Trim	Removes both leading and trailing spaces from a string
UCase	Converts a string to upper-case

Function	Description
Abs	Returns the absolute value of a number
Atn	Returns the arc tangent of a number
Avg	Returns the average value of an expression
Cos	Returns the cosine of an angle
Count	Returns the number of records returned by a select query
Exp	Returns e raised to the power of a specified number
Fix	Returns the integer part of a number
Format	Formats a numeric value with the specified format
Int	Returns the integer part of a number
Max	Returns the maximum value in a set of values
Min	Returns the minimum value in a set of values
Randomize	Initializes the random number generator (used by Rnd()) with a seed
Rnd	Returns a random number
Round	Rounds a number to a specified number of decimal places
Sgn	Returns the sign of a number
Sqr	Returns the square root of a number
Sum	Calculates the sum of a set of values
Val	Reads a string and returns the numbers found in the string

Function	Description
Date	Returns the current system date
DateAdd	Adds a time/date interval to a date and then returns the date
DateDiff	Returns the difference between two dates
DatePart	Returns a specified part of a date (as an integer)
DateSerial	Returns a date from the specified parts (year, month, and day values)
DateValue	Returns a date based on a string
Day	Returns the day of the month for a given date
Format	Formats a date value with the specified format
Hour	Returns the hour part of a time/datetime
Minute	Returns the minute part of a time/datetime
Month	Returns the month part of a given date
MonthName	Returns the name of the month based on a number
Now	Returns the current date and time based on the computer's system date and time
Second	Returns the seconds part of a time/datetime
Time	Returns the current system time
TimeSerial	Returns a time from the specified parts (hour, minute, and second value)
TimeValue	Returns a time based on a string
Weekday	Returns the weekday number for a given date
WeekdayName	Returns the weekday name based on a number
Year	Returns the year part of a given date

Function	Description
CurrentUser	Returns the name of the current database user
Environ	Returns a string that contains the value of an operating system environment variable
IsDate	Checks whether an expression can be converted to a date
IsNull	Checks whether an expression contains Null (no data)
IsNumeric	Checks whether an expression is a valid number

MS Access has many built-in functions.

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

Function	Description
Asc	Returns the ASCII value for the specific character
Chr	Returns the character for the specified ASCII number code
Concat with &	Adds two or more strings together
CurDir	Returns the full path for a specified drive
Format	Formats a value with the specified format
InStr	Gets the position of the first occurrence of a string in another
InstrRev	Gets the position of the first occurrence of a string in another, from the end of string
LCase	Converts a string to lower-case
Left	Extracts a number of characters from a string (starting from left)
Len	Returns the length of a string
LTrim	Removes leading spaces from a string
Mid	Extracts some characters from a string (starting at any position)
Replace	Replaces a substring within a string, with another substring, a specified number of times
Right	Extracts a number of characters from a string (starting from right)
RTrim	Removes trailing spaces from a string
Space	Returns a string of the specified number of space characters
Split	Splits a string into an array of substrings
Str	Returns a number as string
StrComp	Compares two strings
StrConv	Returns a converted string
StrReverse	Reverses a string and returns the result
Trim	Removes both leading and trailing spaces from a string
UCase	Converts a string to upper-case

Function	Description
Abs	Returns the absolute value of a number
Atn	Returns the arc tangent of a number
Avg	Returns the average value of an expression
Cos	Returns the cosine of an angle
Count	Returns the number of records returned by a select query
Exp	Returns e raised to the power of a specified number
Fix	Returns the integer part of a number
Format	Formats a numeric value with the specified format
Int	Returns the integer part of a number
Max	Returns the maximum value in a set of values
Min	Returns the minimum value in a set of values
Randomize	Initializes the random number generator (used by Rnd()) with a seed
Rnd	Returns a random number
Round	Rounds a number to a specified number of decimal places
Sgn	Returns the sign of a number
Sqr	Returns the square root of a number
Sum	Calculates the sum of a set of values
Val	Reads a string and returns the numbers found in the string

Function	Description
Date	Returns the current system date
DateAdd	Adds a time/date interval to a date and then returns the date
DateDiff	Returns the difference between two dates
DatePart	Returns a specified part of a date (as an integer)
DateSerial	Returns a date from the specified parts (year, month, and day values)
DateValue	Returns a date based on a string
Day	Returns the day of the month for a given date
Format	Formats a date value with the specified format
Hour	Returns the hour part of a time/datetime
Minute	Returns the minute part of a time/datetime
Month	Returns the month part of a given date
MonthName	Returns the name of the month based on a number
Now	Returns the current date and time based on the computer's system date and time
Second	Returns the seconds part of a time/datetime
Time	Returns the current system time
TimeSerial	Returns a time from the specified parts (hour, minute, and second value)
TimeValue	Returns a time based on a string
Weekday	Returns the weekday number for a given date
WeekdayName	Returns the weekday name based on a number
Year	Returns the year part of a given date

Function	Description
CurrentUser	Returns the name of the current database user
Environ	Returns a string that contains the value of an operating system environment variable
IsDate	Checks whether an expression can be converted to a date
IsNull	Checks whether an expression contains Null (no data)
IsNumeric	Checks whether an expression is a valid number