Using Expression Builder In Access 2010

Applying different conditions over data fields comes in very useful when you need to evaluate data field values based on other fields. Expression Builder in Access 2010 provides you just that with full support of applying functions and formulas. It can be used anywhere in Access 2010 with any database element like; Forms, Reports, Queries and Tables. In this post we will be using expression builder in query for evaluating new data field values.

To begin with, launch Access 2010 and create a table, navigate to Create tab and click Table. For Illustration, we are creating a table having fields, ID, Employee Name, Year of Service, and Salary.

designview12 Using Expression Builder In Access 2010View in gallery

After assigning data fields in Design view, close it and give it a suitable name. Open table in Datasheet view and start filling in field values.

employeesalary Using Expression Builder In Access 2010View in gallery

We are intending to evaluate new salary based on condition that if employee rendered his services for more than 4 years, he will have a pay raise. For applying this condition, we will be creating a query. Head over to Create tab and click Query Design.

query designView in gallery

A box will pop-up containing table name, select recently created table and click Add.

show tableView in gallery

In inserted table box, double-click the fields which you want to insert in Query.

design queryView in gallery

Now place the insert cursor adjacent to the last field, and from Query Setup group, click Builder.

builderView in gallery

It will bring up Expression Builder dialog, here we will write an expression that will evalaute the new salary as per specified condition.

We have simply used iff conditional statement as;

New Salary: IIf([Year of Service]>4,[Salary]*2.5,[Salary]*1)

It will create a new table field namely New Salary in which you will see new salary of employees. Once done writing condition, click OK to continue.

year of service 1View in gallery

Expression will be successfully added adjacent to last table field. Now under Results group, click Run.

runView in gallery

On Query execution, you will see a new table field namely New Salary, containing new salaries of employees as specified in Expression Builder.

queryView in gallery

Now close the Query and give it a suitable name.

new salaryView in gallery

You can also check out our previously reviewed guides on How to create tables in Access 2010 using only SQL commands & How to encrypt database with password in Access 2010