Ms Access 2010 tutorial:Built-in Functions


Ms Access 2010 tutorial:Built-in Functions

IX. Build-in Functions

IX.1. Numeric Manipulation Functions

Numeric manipulation functions are used with numeric data. The functions include SQTR(), COS(), SIN(), TAN(), TI(), and EXP().

IX.2. Text Manipulation Functions

Text Manipulation Functions are used with text data type. The functions are:

- Left( String, Length): This function is used to take out a substring from the beginning of a String argument. The length of substring is specified by the Length argument that you defined.

Example1: you want cut out two letters of employee names from left to right. To do so, you have to write the following function.

FirstName:Left(EmpName,2)

Microsoft Access 2010 using left functon Microsoft Access 2010 left functon result 

Example2: You want take out all first names of employees from the left. The Employee table has the following data.

Microsoft Access 2010 employees'data 

You see the length of employees' first names are not the same; some are short and some are long. So, to cut out all the employees' last names, you need Instr(start-num, within-text, find-text) function to use with left function. Then, write the following function.

FirstName: Left([EmpName],InStr(1,[EmpName]," "))

Microsoft Access 2010 using left with instr functions 

Result:

Microsoft Access 2010 left with instr functions result 

- Right(String, Length): This function is used to take out a substring from the end of a String argument. The length of substring is specified by the Length argument that you defined.

Example1: You want take out three letter of employee name from right to left. To solve this task, follow the figure below:

Microsoft Access 2010 using right function Microsoft Access 2010 right function result 

Example2: You want take out all last names of employees from right to left. The employees' last names are not the same; some are short, and some are long. Look at the Query12 above. So, to do this task, you also need to use Instr(start-num, within-text, find-text) function like you cut out all first names of employees in example of using left function. Follow the the figure below:

Microsoft Access 2010 using right with instr functions 

Result:

Microsoft Access 2010 right with instr functions result

- Mid(String): This function is used to cut out a substring from the starting number of a String argument. And the length of substring is specified by the Length argument that you defined. For example, you want to take out the middle names of customers. To do so, you need to cut out the customers' first names first; then it is easy for you to cut out the middle names of customers. Create a query in design view and write the follow the function.

FirstName: Left([CusName],InStr(1,[CusName]," "))

MidleName: Mid([CusName],Len([FirstName])+1,InStr(Len([FirstName])+1,[CusName]," ")-Len([FirstName]))

Result:

Microsoft Access 2010 result of using mid function 

- LCASE(String): The function is used to covert a string to lower case letters. For example, you want convert all customer names to lower case letters. To do so, follow the figure below:

Microsoft Access 2010 using LCASE function Microsoft Access 2010 using LCASE function 

- UCASE(String): The function is used to convert a string to capital letters. For instance, you want to convert all customer names to capital letters. To do that, follow the figure below:

Microsoft Access 2010 using UCASE function Microsoft Access 2010 result of using UCASE function 

- Trim(String): The function is used to cut out space from both sides of string. For example, you want to cut out the both sides of spaces from the word " Hello World ". To do that, you have to write the following function.

A=" Hello World "

B= Trim(A)

Result:

B="Hello World"

- LTrim(String): The function is used to take out space from the left side of a string. For instance, you want to cut out the left side of the word " Hello World ".

B= LTrim(A)

Result:

B="Hello World "

- RTrim(String): The function is used to take out space from the right side of a string. For instance, you want to cut out the right side of the word " Hello World ".

B= RTrim(A)

Result:

B=" Hello World"

IX.3. Date/Time Manipulation Functions

The function is used to get the year, month, day and so on.  These functions include:

- DatePart"('Interval', DateArgument): The function is used to get Data/Time data base on interval.

DateArgument: The field name that has data type as Date/Time.

Interval: The form of data that you have to define as Date/Time. The form of interval is described in the table below:

Interval Description
yyyy Year Interval
q Quarter Interval
m Month Interval
d Day Interval
ww Week Interval
w Weekday Interval
h Hour Interval
n Minute Interval
s Second Interval

Example1: You want to display StudentID, StuName, Sex and Year of Birth fields. To do so, follow the figure below:

Microsoft Access 2010 using datepart function 

Result:

Microsoft Access 2010 result of datepart function 

Example2: You want to display students' information including student name, sex, age, and address who live in Phnom Penh. To do this tasks, follow the figure below:

Microsoft Access 2010 calculate students'ages 

Result:

Microsoft Access 2010 result of students'ages 

Note: Date() is a function that is used to get date/time from a computer.

- Datediff('Interval', Argument1, Argument2): The function is used to subtract between argument2 and argument1(Argument2 - Argument1) that have a data type as Date/Time.

For example, you want to calculate the days of course duration. To do so, follow the figure shown below.

Microsoft Access 2010 calculate number of days 

Result:

Microsoft Access 2010 number of days result 

- Day(DateArgument): The function is used to get day from a date.

For instance, you want to display StuName, Address, and the day that the students were born. To do so, follow the figure below:

Microsoft Access 2010 calculate day of birth 

Result:

Microsoft Access 2010 result of day birth 

- Month(DateArgument): The function is used to get month from a date.

- Year(DateArgument): The function is used to get year from a date.




Comments




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.