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.
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.
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.
A box will pop-up containing table name, select recently created table and click Add.
In inserted table box, double-click the fields which you want to insert in Query.
Now place the insert cursor adjacent to the last field, and from Query Setup group, click 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.
Expression will be successfully added adjacent to last table field. Now under Results group, click Run.
On Query execution, you will see a new table field namely New Salary, containing new salaries of employees as specified in Expression Builder.
Now close the Query and give it a suitable name.
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
thank you 🙂
“On Query execution, you will see a new table field”…. where is the On Query Execution command?