BUILDING CONDITIONS

1
BUILDING CONDITIONS

• SIMPLE CONDITIONS
• Relational Operators
• lt less than
• lt less than or equal to
• equal to
• gt greater than or equal to
• ltgt not equal to

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
2
BUILDING CONDITIONS

• SIMPLE CONDITIONS
• a op b
• where a, b can be
• a direct value reference to a field or
  textbox (in ) an arithmetic expression
• price lt 10price pricevat_rate gt
  1000gross_salary - tax gt minimum_salary

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
3
BUILDING CONDITIONS

• SIMPLE IF STATEMENTS
• if condition what to do if the condition is
  trueelse what to do if the condition is
  falseendif
• if an employees yearly gross salary is less than
  3,500,000,000TL income tax is 15, otherwise
  income tax is 20
• if yearly_gross lt 3500000000 income_tax 15
  / 100else income_tax 20 / 100endif

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
4
BUILDING CONDITIONS

• NESTED IF STATEMENTS
• Sometimes, we have a set of many conditions. In
  this case we may need to use Nested If
  Statements.
• Gross salary income tax rateup to
  3,500,000,000TL 15 between 3,500,000,000
  and 5,000,000,000 TL 20
• above 5,000,000,000 TL 25
• if yearly_gross lt 3500000000 income_tax 15
  / 100else
• if yearly_gross lt 5000000000 income_tax
  20 / 100 a nested IF
• else income_tax 25 / 100
• endif
• endif

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
5
BUILDING CONDITIONS

• What we need to remember with nested IF
  statements
• Be careful, what does each else mean?
• Each IF must have its own ENDIF
• if yearly_gross lt 3500000000 income_tax 15
  / 100else /means yearly_gross gt 3500000000
  therefore, we have 2 alternatives
  yearly_gross may be lt 5000000000
  or, yearly_gross may be gt 5000000000 , so we
  need a new IF if yearly_gross lt 5000000000
  income_tax 20 / 100
• else /means yearly_gross gt 5000000000/
  income_tax 25 / 100
• endif
• endif

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
6
BUILDING CONDITIONS

• if yearly_gross lt 3500000000 income_tax 15
  / 100else /means yearly_gross gt 3500000000
  therefore, we have 2 alternatives
  yearly_gross may be lt 5000000000
  or, yearly_gross may be gt 5000000000 , so we
  need a new IF if yearly_gross lt 3500000000
  income_tax 20 / 100
• else /means yearly_gross gt 5000000000/
  income_tax 25 / 100
• endif
• endif
• Written using the IIf statement in Access, for
  the textbox to contain the income tax
• IIf(yearly_gross lt 3500000000 15/100 IIf
  (yearly_gross lt 3500000000 20/100 25/100))

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
7
BUILDING CONDITIONS

• LOGICAL IF STATEMENTS
• Two conditions joined with a logical operator.
• Logical operators
• AND
• OR
• NOT
• Students who got above 50 from the midterm and
  the final
• midterm gt 50 AND final gt 50
• Students who got above 50 from at least one of
  the exams
• midterm gt 50 OR final gt 50
• Students who got above 50 from the midterm but
  not from the final
• midterm gt 50 AND NOT final gt 50

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
8
BUILDING CONDITIONS

• Tricky
• Students in section 1 and section 2
• section 1 AND section 2 wrong! A
  students section can not be both 1 and 2
• the correct statement is
• section 1 OR section 2
• Students whose midterm grade is between 20 and
  70
• midterm gt 20 AND midterm lt 70
• Students whose midterm grade is outside the
  range 20 - 70
• This can be expressed in two ways
• a) Students whose midterm grade is NOT
  between 20 and 70 NOT(midterm gt 20 AND
  midterm lt 70)
• b) Students whose midterm is lt 20 and
  students whose midterm is gt 70
• midterm lt 20 OR midterm gt 70

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
9
BUILDING CONDITIONS

• ECONOMIZE WHEN BUILDING CONDITIONS
• Dont test something which is obvious.
• Example
• If the salary is less than 500, tax rate is 10.
  Otherwise the tax rate is 20
• if salary lt 500
• tax_rate 10/100
• else --gt means salary is not less than 500
• if salary gt 500 ----gt not necessary.
• tax_rate 20/100
• endif
• endif

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
10
BUILDING CONDITIONS

• Example
• If the salary is less than 500, tax rate is 10.
  If the salary is between 500 - 1000 the tax rate
  is 20. If the salary is above 1000, the tax rate
  is 25.
• if salary lt 500
• tax_rate 10/100
• else --gt means salary is not less than 500, but
  we need to check if it is between 500 - 1000, or
  above 1000.
• if salary gt 500 AND salary lt1000
• tax_rate 20/100
• else --gt means salary is not lt1000
• if salary gt 1000
• tax_rate 25/100
• endif
• endif
• endif

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
11
BUILDING CONDITIONS

• Example
• If the salary is less than 500, tax rate is 10.
  If the salary is above 1000, the tax rate is 25.
• if salary lt 500
• tax_rate 10/100
• else --gt means salary is not less than 500, but
  THIS DOES NOT MEAN SALARY IS gt 1000
• if salary gt 1000 necessary!
• tax_rate 25/100
• endif
• endif

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
12
BUILDING CONDITIONS

• Example
• If the salary is less than 500, but the employee
  is retired, tax rate is 10. If the salary is
  less than 500 but the employee is not retired,
  tax rate is 15. If the salary is gt 500, tax
  rate is 20, whether or not the employee is
  retired.
• We can build our condition in two ways

if salary lt 500 and retired True
tax_rate 10/100 else --gt we cant say what
this means if salary lt 500 and retired
False tax_rate 15/100 else --gt we
checked all options for retired, it must be
that salarygt500 if salary gt
500 tax_rate 20/100 endif
endif endif
if salary lt 500 if retired
True tax_rate 10/100 else --gt
means the employee is NOT retired
tax_rate 15/100 endif else
--gt means salary is not less than 500
tax_rate 20/100 endif
N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring