Chapter 7 Exercise 1

Create a small database with tables for Customers and Employees. In addition to name and phone number, each table should hold a date column for when the person first started (as either a customer or hire date). Write a function that returns a percentage discount that uses a phone number to decide if the buyer is a customer or employee. Customers for less than one year get no discount, 1-3 years (2%), 4-7 years (4%), 8 or more years (5%). Employees for less than one year get no discount, 1-2 years (5%), 3-5 years (7%), 6 or more years (10%).

In Access. (Other systems are similar but queries are different.)

Option Compare Database
Option Explicit
Function GetDiscount(ByVal sPhone As String) As Single
  ' See if it is an employee
  Dim rst As New ADODB.Recordset
  Dim sSQL As String, qt As String
  Dim nY As Integer
  qt = """"
  sSQL = "SELECT EmployeeID, DateDiff(" & qt & "yyyy" & qt & ",[DateHired],Date()) AS NYears FROM Employee WHERE Phone=" & qt & sPhone & qt
  rst.Open sSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  If Not rst.EOF Then
    ' it is an employee
    nY = CInt(rst("NYears"))
    rst.Close
    Set rst = Nothing
    If (nY < 1) Then
      GetDiscount = 0#
    ElseIf (nY < 3) Then
      GetDiscount = 0.05
    ElseIf (nY < 6) Then
      GetDiscount = 0.07
    Else
     GetDiscount = 0.1
    End If
    Exit Function
  End If
  rst.Close
  sSQL = "SELECT Customer.CustomerID, DateDiff(" & qt & "yyyy" & qt & ",[DateStarted],Date()) AS NYears FROM Customer WHERE Phone=" & qt & sPhone & qt
  rst.Open sSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  If Not rst.EOF Then
    ' It is an existing customer
    nY = CInt(rst("NYears"))
    rst.Close
    Set rst = Nothing
    If (nY < 1) Then
      GetDiscount = 0#
    ElseIf (nY < 4) Then
      GetDiscount = 0.02
    ElseIf (nY < 8) Then
      GetDiscount = 0.04
    Else
      GetDiscount = 0.05
    End If
    Exit Function
  End If
  rst.Close
  Set rst = Nothing
  GetDiscount = 0#
End Function