Database Management Systems Chapter 7 Calculations and Data

Database Management Systems Chapter 7 Calculations and Data Manipulation Jerry Post Copyright © 1998 1

D A T A B A S E Database Programming ² ² ² ² ² Variables Computations Standard Functions Debug Output Input Conditions Loops Arrays ² ² ² ² ² Data on Forms Properties Events Do. Cmd: Internal Commands Data Transfer Across Forms Data Lookup Programming SQL Database access programming Functions & statements grouped by task 2

D A T A B A S E Programming Environment Tables ² Create code ª (1) In forms and reports ª (2) Within the query system ª (3) Hosted in external programs External Program (3) DBMS Queries Forms & Reports (2) If (. . ) Then SELECT. . . Else. . . UPDATE. . . End If C++ if (. . . ) { // embed SQL SELECT … } (1) If (Click) Then Msg. Box. . . End If 3

D A T A B A S E Programming Appendix Windows Environment ² Before Windows ² With Windows ª Your code did all of the work. ª Programmer In complete control. ª Code ran from top to bottom. Monolithic Code Start here Do this routine If (. . . ) Then print. . . End If Wait for input More routines End here ª Structure and interface are defined by Windows standards. ª Event-driven. Code is in small pieces that are called as needed, when some event occurs. Windows Form Event/Trigger On_Click Msg. Box. . . On_Change UPDATE. . . 4

D A T A B A S E Data on Forms ² Simple assignment ª [control] = value ª value = [control] ² Naming conventions ª Is it a variable or a control? ² Full names ª Forms![myform]![ctl. Tax] ² Null values: ª Is. Null([control]) Form r Afte 32. 50 ctl. Tax Up date Code Sub Compute. Tax Total = … tax. Rate =. . . [ctl. Tax] = tax. Rate*Total End Sub 5

D A T A B A S E Control Properties and Methods ² Set properties with code ² Examples Employee ª Visible/Invisible ª Locked/Unlocked ª Combo box Row. Source ² Methods ª Set. Focus ª Undo ª Requery ² Code examples ª ª [control]. Visible = False [control]. Locked = False [control]. Set. Focus [combo]. Row. Source= “SELECT. . . FROM. . . ” Sub Employee_After. Update() If (Employee = “manager”) Then [cmd. Salary]. Visible = True [cmd. Schedule]. Visible = True Else [cmd. Salary]. Visible = False [cmd. Schedule]. Visible = False End If End Sub 6

D A T A B A S E Transfer Data Across Forms ² Full names: ª Forms! ª [formname 1]! ª [control 1] ² Forms must be open ² Form 1. Visible = True ² Subforms: ª ª Forms! [formname 1]! [subform]. Form! [control 1] 7

D A T A B A S E Common Properties ² Forms ª ª ª ª Record Source Caption Default View Menu/Scroll Bars Navigation/Selection Size/Center Pop Up/Modal/Border ² Controls ª Name ª Control Source ª Format/Decimal/Default ² Controls ª ª ª Input Mask Validation Rule/Text Status Bar Auto Tab/Enter Visible Enabled/Locked Tab Stop/Index Size, Position Back/Fore Color/Effects Border Font 8

D A T A B A S E Common Events ² Forms ª ª ª Current Before/After Insert Before/After Update Delete Before/After Del Confirm Open/Close Load/Unload Activate/Deactivate Got/Lost Focus Click/Double Click Error/Timer ² Controls ª ª ª ª Before/After Update Change Enter/Exit Got/Lost Focus Click Double Click Mouse Key Down/Up/Press 9

D A T A B A S E Do. Cmd: Internal Commands ² See help system ² Common uses ª ª ª ª Find. Next Find. Record Go. To. Control Go. To. Page Go. To. Record Hourglass Maximize/Minimize ª ª ª ª ª Open. Form Open. Query Open. Report Print Quit Run. App (Shell) Run. SQL Set. Warnings Transfer. Database Transfer. Spreadsheet 10

D A T A B A S E Programming: SQL ² Select Into ª New table ² Insert Into ² Operate on sets of data ² Use String for Where ² Use Do. Cmd Run. SQL ª Append rows ² Update ª Change data ² Delete ª Delete rows 11

D A T A B A S E Update ² Changes set of values--based on Where ² Syntax ª Update table SET col 1=val 1, col 2=val 3 Where condition ² Examples ª str. SQL = “Update Order Set Est. Ship. Date=Order. Date+3” ª Do. Cmd Run. SQL str. SQL ª str. SQL = “Update Product Set Price = Price*” ª str. SQL = str. SQL & CStr(1. 0 + [ctl. Increase]) ª str. SQL = str. SQL & “ Where “ & [ctl. Where] ª Do. Cmd Run. SQL str. SQL 12

D A T A B A S E SQL Example: Employee Titles ² Employee Table ² Change titles ª ª EID 2298 9983 2736 Create a form Old title (combo) New title Command button Name Adams Cuervo Dubai Phone 2253 9973 3385 Manager Team Leader Title Manager Supervisor Worker Go ² SQL UPDATE Employee SET Title = " Team Leader " WHERE Title = " Manager ”; 13

D A T A B A S E SQL Example: New Titles UPDATE Employee SET Title = "Team Leader" WHERE Title = "Manager"; Manager Team Leader Go Sub cmd. Go_After. Update Build Update command in a String, using values from Old. Title and New. Title. Execute the command. End Sub 14

D A T A B A S E Build the SQL Update Command Sub cmd. Go_After. Update Dim str. SQL As String str. SQL = "UPDATE Employee SET Title = ” & txt. New. Title & " WHERE Title = ” & cbo. Old. Title _ End Sub str. SQL: UPDATE Employee SET Title = Team Leader WHERE Title = Manager Problem: You need quotation marks around the titles. 15

D A T A B A S E SQL Update Example SQL Sample UPDATE Employee SET Title = "“Team Leader " WHERE Title = "Manager"; Sub cmd. Go_After. Update Dim str. SQL As String, q as String q = Chr(34) ‘ quotation mark str. SQL = "UPDATE Employee SET Title = " & q & [txt. New. Title] & q & " WHERE Title = " & q & [cbo. Old. Title] & q & " ; " Do. Cmd. Set. Warnings False Do. Cmd. Run. SQL str. SQL Do. Cmd. Set. Warnings True _ ‘ Turn off warnings ‘ Execute the SQL command End Sub 16

D A T A B A S E Debug your Query 17

D A T A B A S E Insert Into (1) ² Adds rows to a table ² Syntax ª Insert Into table (col 1, col 2, …) ª Values (value 1, value 2, … Last. Name First. Name Masi Jorge CID Last … 938 Sosa Masi First Phone Javier 8474 Jorge str. SQL = “Insert Into [Customer] (Last, First)” str. SQL = str. SQL & “ Values (“ & “””” & [ctl. Last] & “””” str. SQL = str. SQL & “, ” & “””” & [ctl. First] & “””” & “)” Do. Cmd Run. SQL str. SQL 18

D A T A B A S E Insert Into (2) ² Copies data rows to a second table ² Syntax ª INSERT INTO {new table, columns} SELECT {any SQL} ² Example, move all customers who have not placed orders recently. ª Let users define “recently” by picking the number of days. SQL INSERT INTO Old. Customer SELECT * FROM Customer WHERE Customer. ID NOT IN (SELECT Customer. ID FROM Order WHERE (Odate > Date() - x); 19

D A T A B A S E Code Example for Insert Customer. ID 7763 3635 4456 O# 9987 2275 Name Phone Juarez 9987 Kramer 2285 Ciaro 8474 C# 3635 4456 Odate 02 -15 -98 05 -23 -98 Customer. ID Name Phone 7763 Juarez 9987 … Customers who have not placed an order within some time frame. Dim str. SQL = “INSERT INTO Old. Customer” str. SQL = str. SQL & “ SELECT * FROM Customer WHERE” str. SQL = str. SQL & “ Customer. ID NOT IN” str. SQL = str. SQL & “ (SELECT Customer. ID FROM Order” str. SQL = str. SQL & “ WHERE (Odate > Date() - “ str. SQL = str. SQL & [txt. Days] & “); ” Time frame (txt. Days) Do. Cmd. Run. SQL str. SQL is given by user. 20

D A T A B A S E Delete ² ² Delete a set of rows that match a condition Syntax: Delete From table Where condition Cascade on Delete! Example: Move old customer data str. Where = “Customer. ID NOT IN (SELECT Customer. ID FROM Order” str. Where = str. Where & “ WHERE (Odate > Date() - “ & [txt. Days] & “); ” str. SQL = “INSERT INTO Old. Customer” str. SQL = str. SQL & “ SELECT * FROM Customer WHERE “ & str. Where Do. Cmd. Run. SQL str. SQL ‘ Copy old customer data ‘ To Do: Backup the data in related tables str. SQL = “DELETE FROM Customer WHERE “ & str. Where Do. Cmd. Run. SQL str. SQL ‘ Delete from main table & cascade 21

D A T A B A S E Data Lookup Commands: D. . . ² Syntax ² D. . . (expr, domain, criteria) ª “Column” ª “Table” ª “Where Clause” ² Functions ª ª ª DAvg, DCount, DFirst, DLast, DMin, DMax, DSt. Dev. P, DSum, DVar. P Dlookup Column Table V = DSum(“Balance. Due”, “Customer”, “City=‘Chicago’”) Where Clause ² Usually better to use SQL. ² Or to write DAO code. 22

D A T A B A S E Data Access Object Programming ² Purpose ª Track through table or query one row at a time. ª Data cursor/pointer to active row. ² Why? ª Performance. ª SQL cannot do everything. © Complex calculations. © Compare multiple rows. Year 1995 1996 1997 1998 Sales 104, 321 145, 998 276, 004 362, 736 Move. Next Move. Previous Move. First Move. Last Move Test for Beginning and End of File 23

D A T A B A S E Data Access Object Programming ² Containers ª DBEngine ª Workspaces ª Databases ª Recordsets © Read © Write DBEngine Workspace Database Recordset (query) Dim dbs As Database Dim rst As Recordset Set dbs = Current. DB() Set rst = dbs. Open. Recordset(“my query”) 24

D A T A B A S E Program Structure Set dbs = Current. DB() Choose the database str. SQL = “SELECT … “ Define the query Set rst = dbs. Open. Recordset(str. SQL) Open the query to the first row Do Until (rst. EOF) Read or Write data in the current row rst. Move. Next Loop rst. Close Loop through the query Read data or make changes Go to the next row Repeat Close the query 25

D A T A B A S E Problems with Multiple Users Original Data Modified Data Name Alice Carl Donna Ed Name Alice Neal Carl Donna Ed Sales 444, 321 254, 998 652, 004 411, 736 New row is added--while code is running. Sales 444, 321 333, 229 254, 998 652, 004 411, 736 Set rst = dbs. Open. Recordset(“Data”) Value 1 = rst!Sales ‘ Alice (444, 321) rst. Move. Next ‘ Carl … rst. Move. Previous ‘ ? ? ? Which row 26

D A T A B A S E Table Locator Commands ² Bookmarks ² Move Commands ª ª ª Dim My. Mark As String ª My. Mark = rst. Bookmark . Move. Next. Move. Previous. Move. First. Move. Last. Move n. Rows © (Save position) ª. Move. . . © (Move somewhere else) ª rst. Bookmark = My. Mark ² Location tests ª BOF ª EOF Name Alice Neal Carl Donna Ed © (Return to mark) Sales 444, 321 333, 229 254, 998 652, 004 411, 736 Save position bmk = rst. Bookmark rst. Move. Next rst. Bookmark = bmk 27

D A T A B A S E Recordset Find Commands ² ² ² . Find. First “condition”. Find. Last “condition”. Find. Next “condition”. Find. Previous “condition” Inefficient: Sequential search Use SQL instead rst. Index = “Primary. Key” ² rs. No. Match ² rs. Seek ª Indexed ª One item rst. Seek “=“, keyvalue If (rst. No. Match = False) Then ‘ Make changes End If 28

D A T A B A S E Sample Data Access Code Dim dbl. Sum As Double Dim dbs As Database Dim rst As Recordset Set dbs = Current. DB() Set rst = dbs. Open. Recordset(“Customer”) dbl. Sum = 0. 0 Do Until (rst. EOF) dbl. Sum = dbl. Sum + rst!Balance. Due rst. Move. Next Loop rst. Close Msg. Box “Total Due = “ & dbl. Sum Compute total of Balance. Due. Normally use SQL instead. 29

D A T A B A S E Sample Code to Change Data Dim dbs As Database Dim rst As Recordset Do Until (rst. EOF) rst. Edit rst!Balance. Due = rst!Balance. Due*(1 + [Pct. Increase] ) rst. Update rst. Move. Next Loop rst. Close Add a 10% charge to the Balance. Due for every customer. The 10% value is entered on the form by the user. What happens if the row is already locked? Normally use SQL instead. 30

D A T A B A S E Error Handling ² Errors are events ² Simple code: ² Event Options ª Display error and exit ª Msg. Box Err. Description, , “Title” ª Resume Exit_Label ª On Error Goto [label] ª On Error Goto 0 ª On Error Resume Next ² Resume Options On Error Goto Err. Sub 1 Program code Exit. Sub 1: Exit Sub ª Resume Next ª Resume label Error occurs Err. Sub 1: Msg. Box Err. Description, , ”Errors” Resume Exit. Sub 1 31

D A T A B A S E Alternate Error Handling Sub my. Subroutine On Error Resume Next … Set rst=dbs. Open. Recordset(“data”) If Is. Null(rst) Then … Handle the error End If Error occurs, skip to next line. Test for error, handle it. Exit Sub 32

D A T A B A S E Concurrent Access ² Two processes ª Multiple users or processes changing the same data at the same time. ª Final data will be wrong! ² Force sequential ª Locking ª Delayed, batch updates Receive Payment 1) Read balance 2) Subtract pmt 4) Save new bal. 800 -200 600 ª Receive payment ($200) ª Place new order ($150) ² Initial balance $800 ª Result should be $800 200 + 150 = $750 ª Interference result is either $600 or $950 Customers ID Jones Balance $800 $600 $950 Place New Order 3) Read balance 5) Add order 6) Write balance 800 150 950 33

D A T A B A S E Deadlock ² Deadlock ª Two (or more) processes have placed locks on data and are waiting for the other’s data. ² Many solutions 1) Lock Data A 3) Wait for Data B Data A ª Random wait time ª Global lock manager ª Two-phase commit - messages Data B 2) Lock Data B 4) Wait for Data A 34

D A T A B A S E Lock Manager 35

D A T A B A S E Concurrent Access Customer Using & Adams locked Brown Jones Balance 152. 35 315. 81 115. 67 Running the program to add interest charges generates an error message. Do until rst. EOF rst. Edit rst!Balance = rst!Balance*(1. 0+rate) rst. Update rst. Move. Next Loop 36

D A T A B A S E Errors and Locks On Error Goto Err. Sub 1 … rst. Edit rst!Balance = rst!Balance*1. 10 rst. Update … Exit. Sub 1: Exit Sub If the table is locked, Edit will cause an error. Err. Sub 1: If ( Msg. Box (Err. Description, Vb. Retry. Cancel, "Error (RS)” ) = vb. Retry ) Then Resume Let the user retry the edit or exit. Else To do it automatically, wait Resume Exit. Sub 1 for a random number of seconds. End If 37

D A T A B A S E Post’s Picky Programming • Use a naming convention. • Use proper indentation. • Comment your work. • Avoid spaces in variable names. • Use Option Explicit. • Recompile constantly. • Use as many parentheses as possible. • Split complex conditions. • Make it easy for the user. • Use the status bar and tool tips. • All code must be subject to error trapping. • Use Retry with rst. Edit sections. • Use subroutines and functions to simplify. • Keep backup copies. • Never use a raw number--use Const. • Remember that databases can be moved. • Test applications on different hardware. • Test all calculations by hand. 38

D A T A B A S E Comments dbl. Sum = 0# Do While Not rst. EOF dbl. Sum = dbl. Sum + rst!Balance rst. Move. Next Loop Weak comments ' Initialize the accumulator ' Loop through the table ' Accumulate the balance ' Move to the next row ' End the loop ' Need to compute total balance from sales Useful comments ' Will use a loop instead of SQL ' Because some conditions will be added later dbl. Sum = 0# Do While Not rst. EOF ' Add condition when user provides it, for example ' If this customer has more than three sales past due, ' only count the three most recent (write off the older ones) dbl. Sum = dbl. Sum + rst!Balance rst. Move. Next Loop 39

D A T A B A S E Sally’s Pet Store Main Switchboard Employee logs in. Buttons are presented based on the management level of the employee. Accounting Marketing Employees Not available to this employee. Purchasing forms are accessible by this employee. 40

D A T A B A S E Sally’s Pet Store: Switchboard Logic Event: Employee. ID After. Update On Error Goto Err. EIDAU Declare variables. Lookup assigned Management. Level of employee. Get Management levels for each section. Make two sections of buttons invisible. If (Mgt. Level > Level 1) Then Make first section of buttons visible. If (Mgt. Level > Level 2) Then make second section of buttons visible. End If Exit. EIDAU: Exit Sub Err. EIDAU: Msg. Box Resume Exit. EIDAU 41

D A T A B A S E Sally’s Pet Store: Switchboard Code Private Sub Employee. ID_After. Update() On Error Go. To Err. EIDAU Dim var. Level, var. MGTLEVEL 1, var. MGTLEVEL 2 If Not Is. Null(Employee. ID) Then var. Level = DLookup("Employee. Level", "Employee", _ "Employee. ID=" & [Employee. ID]) If Not Is. Null(var. Level) Then var. MGTLEVEL 1 = DLookup("Value", "Preferences", _ "Key. ID=" & """" & "MGTLEVEL 1" & """") var. MGTLEVEL 2 = DLookup("Value", "Preferences", _ "Key. ID=" & """" & "MGTLEVEL 2" & """") End If cmd. Animal. Purchase. Visible = False cmd. Merchandise. Purchase. Visible = False cmd. Inventory. Visible = False cmd. Accounting. Visible = False cmd. Marketing. Visible = False cmd. Employees. Visible = False 42

D A T A B A S E Sally’s Pet Store: Switchboard Code If (var. Level > Val(var. MGTLEVEL 1)) Then cmd. Animal. Purchase. Visible = True cmd. Merchandise. Purchase. Visible = True cmd. Inventory. Visible = True If (var. Level > Val(var. MGTLEVEL 2)) Then cmd. Accounting. Visible = True cmd. Marketing. Visible = True cmd. Employees. Visible = True End If Exit. EIDAU: Exit Sub Err. EIDAU: Msg. Box Err. Description, , "Unexpected Error (EIDAU)" Resume Exit. EIDAU End Sub 43

D A T A B A S E Sally’s Pet Store: Employees Enter a ZIP code and the form tries to find a matching city. Choose a city and the ZIP code is entered automatically. Spin buttons can be used to set employee level. 44

D A T A B A S E Sally’s Pet Store: Employee Spin Button Private Sub Spin. Level_Spin. Down() If Is. Null(Employee. Level) Then Employee. Level = 0 Else If (Employee. Level > 0) Then Employee. Level = Employee. Level - 1 End If End Sub Private Sub Spin. Level_Spin. Up() If Is. Null(Employee. Level) Then Employee. Level = 1 Else If (Employee. Level < 255) Then Employee. Level = Employee. Level + 1 End If End Sub 45

D A T A B A S E Sally’s Pet Store: City Private Sub City. ID_After. Update() On Error Go. To Err. CIDAU ‘ Requires a large city table. If Is. Null([Zip. Code]) Then [Zip. Code] = DLookup("Zip. Code", "City. ID=" & [City. ID]) End If ‘ Do not replace an existing Zip. Code entry. Exit. CIDAU: Exit Sub Err. CIDAU: Msg. Box Err. Description, , "Unexpected Error (CIDAU)" Resume Exit. CIDAU End Sub 46

D A T A B A S E Sally’s Pet Store: Zip. Code Private Sub Zipcode_After. Update() On Error Go. To Err. ZCAU Dim str. Zip. Short As Variant, new. City. ID As Variant str. Zip. Short = Get 5 Digit. Zip. Code(Zip. Code) new. City. ID = DLookup("City. ID", "City", _ "Zip. Code=" & """" & str. Zip. Short & """") If Not Is. Null(new. City. ID) Then [City. ID] = new. City. ID End If ‘ City table only uses 5 digit codes. Exit. ZCAU: ‘ But we need to store 9 digits in Zip. Code. Exit Sub Err. ZCAU: Msg. Box Err. Description, , "Unexpected Error (ZCAU)" Resume Exit. ZCAU End Sub 47

D A T A B A S E Programming Review: Variables ² Integer ª 2 bytes ª -32768 32767 ² Long ª 4 bytes ª +/- 2, 147, 483, 648 ² Single ª 4 bytes ª +/- 3. 402823 E 38 ª +/- 1. 401298 E-45 ² Global, Const, Static ² Double ª 8 bytes ª +/- 1. 79769313486232 E 308 ª +/- 4. 94065645841247 E-324 ² Currency ª 8 bytes ª +/- 922, 337, 203, 685, 477. 5808 ² String & String*n ² Variant ª Any data type ª Null 48

D A T A B A S E Programming: Scope and Lifetime ² Scope ª Where is the variable, and which procedures can access it? ² Lifetime ª When is the variable created, and when is it destroyed? Different procedures, different variables. Created and destroyed each time the button is clicked. Form Button 1 Button 2 Form--Module Code Sub Button 1_Click() Dim i 1 As Integer i 1 = 3 End Sub Button 2_Click() Dim i 1 As Integer i 1 = 7 End Sub 49

D A T A B A S E Programming: Global Variables ² Wider scope and lifetime ª Created at a higher level © Form © Public module ª Accessible to any procedure in that form or module. ª Declare it Global to make it available to any procedure. Form Button 1 Button 2 Form--Module Code Dim i 2 As Integer Sub Button 1_Click() i 2 = 20 End Sub Variable is created when form is opened. Sub Button 2_Click() Clicking Button 1 sets the i 2 = i 2 + 7 initial value. End Sub Clicking Button 2 modifies the value. What if user clicks buttons in a different order? 50

D A T A B A S E Programming: Computations ² Standard Math ª+ - * / ª Integer divide ª ^ Exponentiation © (2^3 = 2*2*2 = 8) ª Mod © (15 Mod 4 = 3) (12 + 3 = 15) “Frank” & “Rose” “Frank. Rose” Left(“Jackson”, 5) “Jacks” Trim(“ Maria “) “Maria” ² String ª ª ª ª ª & Concatenation Left, Right, Mid Trim, LTrim, RTrim String Chr, Asc LCase, UCase In. Str Len Str. Comp Format Len(“Ramanujan”) 9 String(5, ”a”) “aaaaa” In. Str(“ 8764 Main”, ” “) 5 51

D A T A B A S E Programming: Standard Functions ² Numeric ª ª ª ª Exp, Log Atn, Cos, Sin, Tan Sqr Abs Sgn Int, Fix Rnd, Randomize x = loge (ex) Trigonometric functions ? 2 = 1. 414 =30 92 Abs(-35) 35 Sgn(-35) -1 Int(17. 893) 17 Rnd() 0. 198474 52

D A T A B A S E Programming: Standard Functions: Date/Time ² Date, Now, Time ² Date. Add, Date. Diff ª ª “y”, “m”, “q”. . . Firstweekday 1=Sunday, . . . Can also be used to find number of Fridays, between two dates. 02/19/99 today 03/21/99 Date. Due = Date. Add(“d”, 30, Date()) 53

D A T A B A S E Programming: Standard Functions: Variant ² Variant ª ª ª Is. Date Is. Numeric Var. Type Is. Empty Is. Null 54

D A T A B A S E Programming: Debug ² ² ² Stop Ctrl-Break F 5: Go F 8: Step through S-F 8: Step over Breakpoints ² Immediate Window ª ? or Print ª Any assignment ª Any code 55

D A T A B A S E ² Msg. Box Programming: Output: Message Box ª Message ª Type ª Title ² Types: Use Constants ª ª vb. OKOnly vb. OKCancel vb. Abort. Retry. Ignore vb. Yes. No. Cancel vb. Yes. No vb. Retry. Cancel ² Defaults ² Icons ª ª vb. Critical Stop sign vb. Question mark vb. Exclamation Warning vb. Information Circle i ² Responses ª ª vb. OK vb. Cancel vb. Abort vb. Retry vb. Ignore vb. Yes vb. No ª vb. Default. Button 1 ª vb. Default. Button 2 ª vb. Default. Button 3 Msg. Box "This is a message box", vb. Yes. No. Cancel + vb. Information, "Sample Box" 56

D A T A B A S E ² Input. Box ª ª Programming: Input. Box Prompt Title Default X-Pos, Y-Pos ² Prompt ª Cannot change box size ª Use Chr(10) & Chr(13) for blank lines. ² Returns text or Variant ² Cancel = zero string ““ ² Positions ª ª Twips Twentieth of inch point 72 points 1440 twips per inch Dim str As String str = Input. Box( "Enter your name: ", "Sample Input", , 5000) 57

D A T A B A S E Programming: Conditions ² If ² Conditions ª If (Condition) Then © statements for true ª Else ª <, <=, >, >=, =, <> ª And, Or, Not, Xor ª Eqv, Imp (logic) © statements for false ª End If ² IIF (Cond. , True, False) ² Select Case (expr) ª Case value © statements ª Case value 2 ª Case Else ª End Select If (Condition 1) Then statements for true Else statements for false If (Condition 2) Then statements for true End If 58

D A T A B A S E Programming Select Example ² Message Box ² Could use repeated If statements ² Better to use Select Case response = Msg. Box(…) If (response == vb. Yes) Then ‘ statements for Yes Else If (response == vb. No) Then ‘ statements for No Else ‘statements for Cancel End If response = Msg. Box(…) Select Case response Case vb. Yes ‘ statements for Yes Case vb. No ‘ statements for No Case vb. Cancel ‘ statements for Cancel End Case 59

D A T A B A S E Programming: Loops Initialize value Statements ² Do ² For … Next ² For Each Change value Test condition Do Until (x > 10) Do While (x <= 10) ‘ Statements x=x+1 Loop Do For x = 1 to 10 ‘ Statements x=x+1 ‘ Statements Next x Loop Until (x > 10) 60

D A T A B A S E Programming: Loops Again ² Do ² For/Each (objects) ª Do {While | Until} © Exit Do (optional) ª Loop ª For Each element In group © [Exit For] (optional) ª Next element ² With (objects) ª Do ª Loop {While | Until} ª With object ª End With ² For/Next ª For counter = start To end Step increment © Exit For (optional) ª Next counter 61

D A T A B A S E Programming Subroutines and Functions ² Sub name (var 1 As. . . , var 2, . . . ) ² End Sub ² Function fname (var 1 As. . . ) As datatype ª fname = … ‘ returns a specific value ² End Function ² Variables are passed by reference ª Changes made to the parameters in the subroutine are passed back to the caller. ² Unless you use By. Val ª Changes are made to a copy of the parameter, but are not returned to the calling program. 62

D A T A B A S E Programming: Example Subroutine Main program … Status. Message “Trying to connect. ” … Status. Message “Verifying access. ” … End main program Sub Status. Message (Msg As String) ‘ Display Msg, location, color End Sub 63

D A T A B A S E Programming: Parameter Types Main j=3 Do. Sum j … ‘ j is now equal to 8 Subroutine Do. Sum (j 2 As Integer) j 2 = 8 End Sub Main j=3 Do. Sum j … ‘ j is still equal to 3 Subroutine Do. Sum (By. Val j 2 As Integer) j 2 = 8 End Sub By Reference Changes to data in the subroutine are passed back. By Value Creates a copy of the variable, so changes are not returned. 64

D A T A B A S E Programming Arrays and User Types u Arrays l l u u Dim array(sub, . . . ) As type Dim i. Sorts(10) As Integer l l (lower To upper, . . . ) Re. Dim [Preserve] array. . . Option Base 0 | 1 v 2. 0 arrays less than 64 KB Type Tname v v Specifying bounds: l User defined types l u u u ename 1 As type ename 2 As type End Type Dim var 1 As Tname var 1. ename 1 =. . . var 1. ename 2 =. . . 65

D A T A B A S E Programming: Financial Functions u Fixed payments l l l l u u u PV (rate, nper, pmt, fv, due) FV (rate, nper, pmt, pv, due) IPmt (rate, per, nper, pv, fv, due) NPer (rate, pmt, pv, fv, due) Pmt (rate, nper, pv, fv, due) PPmt (rate, per, nper, pv, fv, due) Rate (nper, pmt, pv, fv, due, guess) u Arrays l l l u NPV (rate, array) IRR (array, guess) MIRR (array, finrate, re_rate) Depreciation l l l DDB (cost, salv, life, period) SLN (cost, salvage, life) SYD (cost, salv. , life, period) rate interest rate period per specific period number nper # of periods pv present value fv future value due 0=due at end, 1=due at start 66

D A T A B A S E Programming: Text File Input/Output ² ² ² ² Open filename As # file# Close # file#, Reset Print #, Put, Write Spc, Tab Get, Input #, Line Input # EOF, LOF Seek # file#, position ² ² ² Ch. Dir, Ch. Dirve Dir Kill, (re)Name Lock, Unlock Cur. Dir, Mk. Dir, Rm. Dir 67

D A T A B A S E DDE: Dynamic Data Exchange ² ² Shell DDEInitiate DDEExecute DDEPoke, DDE Send ² ² Application must be running Start a conversation/topic Issue a command Place data ª Send data ² DDE, DDERequest ² Get data ª Request data ² DDETerminate ² Close the session 68

D A T A B A S E OLE: Object Linking & Embedding ² Create. Object (class) ª “appname. objecttype” ² Get. Object (file, class) ² Methods and syntax are defined by the software that exports the object. ² Example ª Dim obj As Object ª set obj = Create. Object(“Word. Basic”) ª obj. Bold ª obj. Insert “text” ª obj. Save. As “file” 69