THE WORLD'S LARGEST WEB DEVELOPER SITE

SQL Tutorial

SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL Union SQL Group By SQL Having SQL Exists SQL Any, All SQL Select Into SQL Insert Into Select SQL Case SQL Null Functions SQL Stored Procedures SQL Comments

SQL Database

SQL Create DB SQL Drop DB SQL Backup DB SQL Create Table SQL Drop Table SQL Alter Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key SQL Check SQL Default SQL Index SQL Auto Increment SQL Dates SQL Views SQL Injection SQL Hosting

SQL References

SQL Keywords MySQL Functions SQL Server Functions MS Access Functions SQL Operators SQL Data Types SQL Quick Ref

SQL Examples

SQL Examples SQL Exercises SQL Certificate

MS Access Functions


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


MS Access Numeric Functions

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

MS Access Date Functions

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

MS Access Some Other Functions

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