Flash cards
Review the key moves
1/3
Core idea
What is the main idea behind MS Access Functions?
Lesson checks
Practice each idea before moving on
Short Mimo-style checks built from this lesson's code, terms, and sequence.
1Quick choice
Which statement best captures the main point of this lesson?
2Order
Put the learning moves in the order that makes the concept easiest to apply.
MS Access Numeric Functions
MS Access String Functions
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 |