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.

design view 1

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.

employee salary

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 design

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

show table

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

design query

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


builder

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 1

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

run

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

query

Now close the Query and give it a suitable name.

new salary

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

Advertisement