Topic 11: Using Expressions in Queries

Go to Topic 12 * Access Database Skills Table of Contents * Technology Skills

In Access queries an "expression" can use data from the fields in your database tables to calculate new numeric or text values. Expressions can use addition, subtraction, multiplication and division or other functions, or can use text functions such as Len, Left, Right, Mid, etc. Expressions can also use "if" statements. For example:

Create a full name field based on first and last name:
FullName: Lastname & ", " & Firstname

Calculate hourly pay based on weekly pay and hours per week:
HourlyPay: WeeklyPay / HrsPerWeek

Show the first letter of student last name:
FirstLetter: Left(Lastname,1)

Calculate weeks worked between a StartDate and an EndDate:
WeeksWorked: (EndDate-StartDate+1)/7

Create a field called Biology that is set equal to 1 if the student is taking biology and is set equal to 0 otherwise.:
Biology: iif(course1="BIO101" or course2="BIO101" or course3="BIO101" or course4="BIO101" or course5="BIO101",1,0)