1 / 12

BUILDING CONDITIONS

BUILDING CONDITIONS. SIMPLE CONDITIONS Relational Operators: < less than <= less than or equal to = equal to >= greater than or equal to <> not equal to. N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring. BUILDING CONDITIONS. SIMPLE CONDITIONS a op b

elga
Download Presentation

BUILDING CONDITIONS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BUILDING CONDITIONS SIMPLE CONDITIONS Relational Operators: < less than <= less than or equal to = equal to >= greater than or equal to <> 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] <= 10[price] + [price]*[vat_rate] > 1000[gross_salary] - [tax] >= [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 employee’s yearly gross salary is less than 3,500,000,000TL income tax is 15%, otherwise income tax is 20% if [yearly_gross] < 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] < 3500000000[income_tax] = 15 / 100else if [yearly_gross] < 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] < 3500000000[income_tax] = 15 / 100else /*means [yearly_gross] >= 3500000000 therefore, we have 2 alternatives: [yearly_gross] may be < 5000000000 or, [yearly_gross] may be >= 5000000000 , so we need a new IF:if [yearly_gross] < 5000000000[income_tax] = 20 / 100 else /*means [yearly_gross] >= 5000000000*/[income_tax] = 25 / 100 endif endif N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

  6. BUILDING CONDITIONS if[yearly_gross] < 3500000000[income_tax] = 15 / 100else /*means [yearly_gross] >= 3500000000 therefore, we have 2 alternatives: [yearly_gross] may be < 5000000000 or, [yearly_gross] may be >= 5000000000 , so we need a new IF:if [yearly_gross] < 3500000000[income_tax] = 20 / 100 else /*means [yearly_gross] >= 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] < 3500000000; 15/100; IIf ([yearly_gross] < 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] > 50 AND [final] > 50 * Students who got above 50 from at least one of the exams [midterm] > 50 OR [final] > 50 * Students who got above 50 from the midterm but not from the final [midterm] > 50 ANDNOT [final] > 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 student’s 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] >= 20 AND [midterm] <= 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] >= 20 AND [midterm] <= 70) b) Students whose midterm is < 20 and students whose midterm is > 70 [midterm] < 20 OR [midterm] > 70 N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

  9. BUILDING CONDITIONS ECONOMIZE WHEN BUILDING CONDITIONS Don’t 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] < 500 [tax_rate] = 10/100 else --> means salary is not less than 500 if [salary] >= 500 ----> 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] < 500 [tax_rate] = 10/100 else --> means salary is not less than 500, but we need to check if it is between 500 - 1000, or above 1000. if [salary] >= 500 AND [salary] <=1000 [tax_rate] = 20/100 else --> means salary is not <=1000 if [salary] > 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] < 500 [tax_rate] = 10/100 else --> means salary is not less than 500, but THIS DOES NOT MEAN SALARY IS > 1000 if [salary] > 1000 necessary! [tax_rate] = 25/100 endif endif N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

  12. BUILDING CONDITIONS if [salary] < 500 and [retired] = True [tax_rate] = 10/100 else --> we can’t say what this means if [salary] < 500 and [retired] = False [tax_rate] = 15/100 else --> we checked all options for [retired], it must be that [salary]>=500 if [salary] >= 500 [tax_rate] = 20/100 endif endif endif if [salary] < 500 if [retired] = True [tax_rate] = 10/100 else --> means the employee is NOT retired [tax_rate] = 15/100 endif else --> means salary is not less than 500 [tax_rate] = 20/100 endif 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 >= 500, tax rate is 20%, whether or not the employee is retired. We can build our condition in two ways: N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

More Related