Home

Date and Time-Based Data

Date-Based Fields

Introduction

A field can be made to request and/or display date only, time only, or both date and time values. The visually create a field for date values:

Date and Time

Author Note

Database: Watts' A Loan

We will start a new database named Watts' A Loan. It is a fictitious company that provides small to mid-level loans to persons and small businesses. An individual can request a small loan in cash. The company also a channel of partners as furniture stores, musical instruments stores, car dealers, boat manufacturers, tractor sellers, etc. A person who applies for financing in one of those stores may get a loan from Watts' A Loan that will actually finance the loan.

Practical LearningPractical Learning: Introducing Date-Based Fields

  1. Start Microsoft Access
  2. Click Blank Desktop Database
  3. Set the file name to WattsALoan
  4. Click Create
  5. On the Ribbon, click File and click Options
  6. In the left list of the Access Options dialog box, click Current Database
  7. In the main list, click Overlapping Windows
  8. Click OK on the dialog box
  9. Click OK on the message box
  10. On the Ribbon, click File and click Close
  11. In the list of files, click WattsALoan1
  12. On the Ribbon, click Create and click Form Design
  13. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Watts' A Loan - Loan Evaluation
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Border Style: Dialog
  14. Right-click the body of the form and click Form Header/Footer
  15. Save the form as LoanEvaluation
  16. Design the form as follows:

    Watts' A Loan - Loan Evaluation - Form Design

    Control Caption Name Other Properties
    Label Label Watts' A Loan    
    Label Label Loan Evaluation    
    Text Box Text Box Loan Amount: txtLoanAmount  
    Text Box Text Box Interest Rate: txtInterestRate  
    Label Label %    
    Text Box Text Box Periods txtPeriods  
    Label Label Months    
    Button Button Calculate cmdCalculate  
    Line Line      
    Text Box Text Box Periodic Payment: txtPeriodicPayment  
    Label Label /Month    
    Text Box Text Box Interest Amount: txtInterestAmount  
    Text Box Text Box Future Value: txtFutureValue  
    Button Button Close cmdClose  
  17. On the form, right-click the Calculate button and click Build Event...
  18. In the Choose Builder dialog box, click Code Builder and click OK
  19. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim periods
        Dim loanAmount
        Dim futureValue
        Dim interestRate
        Dim interestPaid
        Dim interestAmount
        Dim periodicPayment
        
        loanAmount = CDbl(Nz(txtLoanAmount))
        interestRate = CDbl(Nz(txtInterestRate)) / 100#
        periods = CDbl(Nz(txtPeriods))
    
        periodicPayment = Pmt(interestRate / 12#, periods, -loanAmount)
        futureValue = FV(interestRate / 12#, periods, -periodicPayment)
        interestAmount = futureValue - loanAmount
        
        txtPeriodicPayment = FormatNumber(periodicPayment)
        txtFutureValue = FormatNumber(futureValue)
        txtInterestAmount = FormatNumber(interestAmount)
    End Sub
  20. In the Object combo box, select cmdClose
  21. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  22. Close the form
  23. When asked whether you want to save, click Yes
  24. On the Ribbon, click Create and, in the Forms section, click Form Design
  25. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Watts' A Loan
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Min Max Buttons: Min Enabled
  26. Save the form as Watts' A Loan
  27. Complete the design of the form as follows:

    Watts' A Loan - Database Switchboard

    Control Name Caption
    Label Label   Tables
    Label Label   Records
    Line Line    
    Button Button cmdLoansAllocations Loans Allocations
    Button Button cmdLoanAllocation Loan Allocation . . .
    Button Button cmdPayments Payments
    Button Button cmdPayment Payment . . .
    Button Button cmdLoansTypes Loans Types
    Button Button cmdEmployeesRecords Employees...
    Button Button cmdTimeSheets Time Sheets
    Button Button cmdNewTimeSheet New Time Sheet . . .
    Button Button cmdPayrolls Payrolls
    Button Button cmdPayroll Payroll . . .
    Button Button cmdEmployeesTable Employees
    Button Button cmdClose Close
  28. On the form, below the Tables label, right-click the Loans Types and click Build Event...
  29. In the Choose Builder dialog box, double-click Code Builder and impliment the event as follows:
    Private Sub cmdLoansTypes_Click()
        DoCmd.RunSQL "CREATE TABLE LoansTypes" & _
                     "(" & _
                     "    LoanType    TEXT(25), " & _
                     "    Description LONGTEXT), " & _
                     "    CONSTRAINT PK_LoansTypes PRIMARY KEY(LoanType)" & _
                     ");"
        DoCmd.RunSQL "INSERT INTO LoansTypes VALUES('Personal Loan', 'This is loan given as a cashier check to a customer who wants a cash loan.')"
        DoCmd.RunSQL "INSERT INTO LoansTypes VALUES('Car Financing', 'This loan will be processed by our partners as car dealers.')"
        DoCmd.RunSQL "INSERT INTO LoansTypes(LoanType) VALUES('Boat Purchase')"
        DoCmd.RunSQL "INSERT INTO LoansTypes(LoanType) VALUES('Furniture')"
        DoCmd.RunSQL "INSERT INTO LoansTypes VALUES('Musical Instrument', 'We have some partnerships in musical instruments stores. This is the type of loan we will make available to the customers they find for us.')"
        
        cmdLoansTypes.Enabled = False
    End Sub
  30. In the Object combo box, select cmdEmployeesTable
  31. Impliment the event as follows:
    Private Sub cmdEmployeesTable_Click()
        DoCmd.RunSQL "CREATE TABLE Employees" & _
                     "(" & _
                     "    EmployeeNumber TEXT(10), " & _
                     "    FirstName      Text(25), " & _
                     "    LastName       text(25), " & _
                     "    HourlySalary   double,   " & _
                     "    Title          text(50), " & _
                     "    CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber)" & _
                     ");"
    
        DoCmd.RunSQL "INSERT INTO Employees(EmployeeNumber, FirstName, LastName, HourlySalary, Title) VALUES('293-747', 'Catherine', 'Watts', 34.15, 'Owner - General Manager');"
        DoCmd.RunSQL "INSERT INTO Employees VALUES('836-486', 'Ernest',   'Thomas',   12.22, 'Accounts Representative');"
        DoCmd.RunSQL "INSERT INTO Employees VALUES('492-947', 'Sandrine', 'Ethridge', 28.74, 'Assistant Manager');"
        DoCmd.RunSQL "INSERT INTO Employees VALUES('240-750', 'Helene',   'Gustman',  14.52, 'Accounts Representative');"
        DoCmd.RunSQL "INSERT INTO Employees VALUES('804-685', 'Melissa',  'Browns',   17.38, 'Customer Accounts Representative');"
        DoCmd.RunSQL "INSERT INTO Employees VALUES('429-374', 'Jake',     'Leighton', 30.26, 'Accounts Manager');"
    
        cmdEmployeesTable.Enabled = False
    End Sub

Date-Based Fields in SQL

To programmatically create a field that would hold date and/or time values in SQL, set the column's data type to either the DATE or the DATETIME types. Here are examples:

Private Sub cmdTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees(" & _
                 "	FullName Text, " & _
                 "	DateHired Date, " & _
                 "	DateLastReviewed DateTime);"
                 
    MsgBox "A table named Employees has been created."
End Sub

Both data types have the same effect in Microsoft Access.

Practical Learning: Introducing Date-Based Fields

  1. In the Object combo box, select cmdLoansAllocations
  2. Implement the event as follows:
    Private Sub cmdLoansAllocations_Click()
        DoCmd.RunSQL "CREATE TABLE LoansAllocations" & _
                     "(" & _
                     "    LoanNumber         AUTOINCREMENT(100001, 1), " & _
                     "    DateAllocated      DATE,     " & _
                     "    EmployeeNumber     TEXT(10), " & _
                     "    CustomerFirstName  Text(25), " & _
                     "    CustomerLastName   text(25), " & _
                     "    LoanType           TEXT(25), " & _
                     "    LoanAmount         Double,   " & _
                     "    InterestRate       Double,   " & _
                     "    Periods		 Double,   " & _
                     "    InterestAmount     Double,   " & _
                     "    MonthlyPayment     Double,   " & _
                     "    FutureValue	 Double,   " & _
                     "    PaymentStartDate   date,     " & _
                     "    CONSTRAINT         FK_LoansProcessors FOREIGN KEY(EmployeeNumber) " & _
                     "        REFERENCES     Employees(EmployeeNumber), " & _
                     "    CONSTRAINT         FK_LoansTypes FOREIGN KEY(LoanType) " & _
                     "        REFERENCES     LoansTypes(LoanType), " & _
                     "    CONSTRAINT         PK_LoansAllocations PRIMARY KEY(LoanNumber)" & _
                     ");"
                     
        cmdLoansAllocations.Enabled = False
    End Sub
  3. In the Object combo box, select cmdPayments
  4. Implement the event as follows:
    Private Sub cmdPayments_Click()
        DoCmd.RunSQL "CREATE TABLE Payments" & _
                     "(" & _
                     "    ReceiptNumber   COUNTER(1001, 1) NOT NULL, " & _
                     "    PaymentDate     DateTime, " & _
                     "    EmployeeNumber  text(10), " & _
                     "    LoanNumber      Long,     " & _
                     "    AmountPaid      Double,   " & _
                     "    Balance         Number,   " & _
                     "    CONSTRAINT      FK_PaymentsProcessors FOREIGN KEY(EmployeeNumber) " & _
                     "        REFERENCES  Employees(EmployeeNumber), " & _
                     "    CONSTRAINT      FK_LoansPayments FOREIGN KEY(LoanNumber) " & _
                     "        REFERENCES  LoansAllocations(LoanNumber), " & _
                     "    CONSTRAINT      PK_Payments Primary Key(ReceiptNumber)" & _
                     ");"
                     
        cmdPayments.Enabled = False
    End Sub
  5. In the Object combo box, select cmdTimeSheets
  6. Implement the event as follows:
    Private Sub cmdTimeSheets_Click()
        DoCmd.RunSQL "CREATE TABLE TimeSheets" & _
                     "(" & _
                     "    TimeSheetID    AutoIncrement(10001, 1),          " & _
                     "    EmployeeNumber varchar(10),                      " & _
                     "    StartDate      varchar(40),                      " & _
                     "    Week1Monday    double, Week1Tuesday   double,    " & _
                     "    Week1Wednesday double, Week1Thursday  double,    " & _
                     "    Week1Friday    double, Week1Saturday  double,    " & _
                     "    Week1Sunday    double, Week2Monday    double,    " & _
                     "    Week2Tuesday   double, Week2Wednesday double,    " & _
                     "    Week2Thursday  double, Week2Friday    double,    " & _
                     "    Week2Saturday  double, Week2Sunday    double,    " & _
                     "    CONSTRAINT     FK_TimeKeepers FOREIGN KEY(EmployeeNumber) " & _
                     "        REFERENCES Employees(EmployeeNumber), " & _
                     "    CONSTRAINT     PK_TimeSheets Primary Key(TimeSheetID)" & _
                     ");"
                     
        cmdTimeSheets.Enabled = False
    End Sub
  7. In the Object combo box, select cmdPayrolls
  8. Implement the event as follows:
    Private Sub cmdPayrolls_Click()
        DoCmd.RunSQL "CREATE TABLE Payrolls" & _
                     "(" & _
                     "    PayrollID           AutoIncrement(100001, 1),                " & _
                     "    StartDate           varchar(40),                             " & _
                     "    EmployeeNumber      varchar(10),                             " & _
                     "    EmployeeName        varchar(84),   HourlySalary      double, " & _
                     "    RegularTime         double,        RegularPay        double, " & _
                     "    Overtime            double,        OvertimePay       double, " & _
                     "    GrossPay            double,                                  " & _
                     "    FederalTax          double,        SocialSecurityTax double, " & _
                     "    MedicareTax         double,        StateTax          double, " & _
                     "    NetPay              double,                                  " & _
                     "    PayDate             varchar(40),                             " & _  
                 "    CONSTRAINT          FK_PayrollReceivers FOREIGN KEY(EmployeeNumber) " & _
                     "        REFERENCES      Employees(EmployeeNumber), " & _
                     "    CONSTRAINT          PK_Payrolls Primary Key(PayrollID)" & _
                     ");"
        
        cmdPayrolls.Enabled = False
    End Sub
  9. Return to Microsoft Access and switch the form to Form View
  10. Below the Tables label on the form, click Employees (and click OK every time a record is created), Loans Types (and click OK every time a record is created), Loans Allocations, Payments, Time Sheets, and Payrolls
  11. Close the forms
  12. When asked whether you want to save, click Yes
  13. On the Ribbon, click Database Tools and click Relationships
  14. Relate the tables

    Relationships

  15. To close the Relationships window, click its Close button Close
  16. On the Ribbon, click Create and click Form Design
  17. In the Property Sheet, click the All tab and change the following characteristics:
    Record Source: Employees
    Caption: Watts' A Loan - Employees
    Auto Center: Yes
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
  18. Right-click the body of the form and click Form Header/Footer
  19. Complete the design of the form as follows:

    Watts' A Loan - Employees

  20. Right-click the Close the button and click Build Event...
  21. In the Choose Builder dialog box, click Code Builder and click OK
  22. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  23. Return to Microsoft Access
  24. Save and close the form
  25. On the Ribbon, click Create and click Form Design
  26. Double-click the Properties button Properties of the form
  27. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Watts' A Loan - Loan Allocation
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
  28. Right-click the body of the form and click Form Header/Footer
  29. Set the Back Color of the Form Header section to Blue-Gray, Text 2, Darker 50%
  30. Set the Back Color of the Detail section to Background Form
  31. Set the Back Color of the Form Footer section to Black, Text 1
  32. Save the form as LoanAllocation
  33. Design the form as follows:

    Watts' A Loan - Loans Allocations - Form Design

    Control Caption Name Other Properties
    Label Label Watts' A Loan    
    Label Label New Loan Allocation    
    Label Label Processed By   Back Color: #727272
    Text Box Text Box Employee #: txtEmployeeNumber  
    Label Label Processed For Customer   Back Color: #727272
    Text Box Text Box First Name:: txtCustomerFirstName  
    Text Box Text Box Last Name: txtCustomerLastName  
    Line Line     Border Width: 2 pt
    Border Color: Dark Gray 5
    Text Box Text Box Loan Type: cbxLoansTypes  
    Text Box Text Box Loan Amount: txtLoanAmount  
    Text Box Text Box Interest Rate: txtInterestRate  
    Text Box Text Box Periods (Months): txtPeriods  
    Button Button Evaluate Loan cmdEvaluateLoan  
    Line Line     Border Width: 2 pt
    Border Color: Dark Gray 5
    Text Box Text Box Interest Amount: txtInterestAmount  
    Text Box Text Box Future Value: txtFutureValue  
    Text Box Text Box Monthly Payment: txtMonthlyPayment  
    Button Button Submit cmdSubmit  
    Button Button Close cmdClose  
  34. On the form, right-click the Evaluate Loan button and click Build Event...
  35. In the Choose Builder dialog box, click Code Builder and click OK
  36. Implement the event as follows:
    Private Sub cmdEvaluateLoan_Click()
        DoCmd.OpenForm "LoanEvaluation"
    End Sub
  37. Return to Microsoft Access
  38. Save and close the form
  39. On the Ribbon, click Create and click Form Design
  40. Double-click the Properties button Properties of the form
  41. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Watts' A Loan - New Payment
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
  42. Right-click the body of the form and click Form Header/Footer
  43. Set the Back Color of the Form Header section to Blue-Gray, Text 2, Darker 50%
  44. Set the Back Color of the Detail section to Background Form
  45. Set the Back Color of the Form Footer section to Black, Text 1
  46. Save the form as NewPayment
  47. Complete the design of the form as follows:

    Watts' A Loan - Loans Allocations - Form Design

    Control Caption Name Other Properties
    Label Label Watts' A Loan    
    Label Label New Payment    
    Label Label Payment Processed By   Back Color: #727272
    Text Box Text Box Employee #: txtEmployeeNumber  
    Label Label Payment Processed For   Back Color: #727272
    Text Box Text Box Loan Number:: txtLoanNumber  
    Text Box Text Box   txtLoanDetails  
    Text Box Text Box Amount Paid: txtAmountPaid  
    Label Label Balance   Back Color: #727272
    Text Box Text Box Before Payment: txtBalanceBeforePayment  
    Text Box Text Box After Payment: txtBalanceAfterPayment  
    Button Button Submit cmdSubmit  
    Button Button Close cmdClose  
  48. Save the form

Formatting a Date Value

Date-Based Masks

A mask is a technique of creating sections, also called placeholders, in a field. Using masks, you can create a text box that requests and/or displays only date values. You can create the text box in a table and transfer it to a form. If the form is independent, you can also create the text box on the form.

To create a text box that supports date values:

In both cases, the field will be equipped with a browse button: Browse. Click it. Continue with the wizard to the Finish page.

The Formats of a Date Value

The format of a date specifies the scheme by which the value will display on the form or report.

To specify the format of a date, if you are creating the table in the Datasheet View, click under Click to Add or click a cell in the column that will precede the new column. On the Ribbon, click Fields. In the Add & Delete section, click More Fields. In the Date and Time section, click the desired option (Short Date, Medium Date or Long Date):

Field Size

If a field was created already with the regular Date & Time option in the Datasheet View or the Date/Time data type in the Design View, to specify or change its format:

If you are using the Input Mask Wizard, in the first page of the wizard, click either Short Date or Medium Date:

Practical Learning: Introducing Dates Formats

  1. The NewPayment form should still be opened in the Design View.
    In the Controls section of the Ribbon, click the Text Box Text Box and click an unoccupied area of the Detail section of the form
  2. Change the Caption of the accompanying label to Payment Date:
  3. In the Property Sheet, change the following characteristics:
    Name: txtPaymentDate
    Format: Long Date
    Text Align: Left
  4. Complete the design of the form as follows:

    Watts' A Loan - Loans Allocations - Form Design

  5. Save and close the form
  6. In the Navigation Pane, right-click NewLoanAllocation and click Design View
  7. In the Controls section of the Ribbon, click the Text Box Text Box and click an unoccupied area of the Detail section of the form
  8. Change the Caption of the accompanying label to Date Allocated:
  9. In the Property Sheet, change the following characteristics:
    Name: txtDateAllocated
    Format: Long Date
    Text Align: Left
  10. In the Controls section of the Ribbon, click the Text Box Text Box and click an unoccupied area of the Detail section of the form
  11. Change the Caption of the accompanying label to Payment Start Date:
  12. In the Property Sheet, change the following characteristics:
    Name: txtPaymentStartDate
    Format: Long Date
    Text Align: Left
  13. Complete the design of the form as follows:

    Watts' A Loan - Loans Allocations - Form Design

  14. Save and close the form

Data Entry on a Date-Based Field

Introduction

Microsoft Access provides two techniques to assist the user in specifying the value of a date-based field. To make data entry easy, if you had specified the data type of a field as Date/Time or Date & Time, in both the table and the form, the field or text box becomes equiped with a calendar. The user can click the calendar and select the desired date. If the field was created using the input mask, the user can click the text box and follow the rules of date values.

Date-Based Data Entry in SQL

In the SQL, to perform data entry on a date or time field, you should/must use an appropriate formula with the year represented by 2 or 4 digits. You should also include the date between # and #. Use any of the formulas we reviewed:

#mm-dd-yy#
#mm/dd/yy#
#mm/dd/yyyy#
#mm-dd-yyyy#
#yyyy/mm/dd#
#yyyy-mm-dd#

Here are examples:

Private Sub cmdCreateRecord_Click()
    DoCmd.RunSQL "INSERT INTO Employees VALUES('Annette Schwartz', #22-10-09#, #11/22/09#)"
    MsgBox "A record has been added to the Employees table."

    DoCmd.RunSQL "INSERT INTO Employees VALUES('Mark Drowns', #2009-06-02#, #2009/06/28#)"
    MsgBox "A record has been added to the Employees table."

    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES('Spencer Harland', '03-08-09', '03/28/09')"
    MsgBox "A record has been added to the Employees table."
    
    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES('Kevin Sealans', '2009-07-20', '2009/08/18')"
    MsgBox "A record has been added to the Employees table."

    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES('Anselme Bows', #09-13-2009#, '10/10/2009')"
    MsgBox "A record has been added to the Employees table."
    
    DoCmd.RunSQL "INSERT INTO Employees " & _
                 "VALUES('Jeremy Huissey', '07-20-2009', #08/18/2009#)"
    MsgBox "A record has been added to the Employees table."
End Sub

Practical Learning: Creating Date-Based Records

  1. In the Navigation Pane, right-click LoanAllocation and click Design View
  2. On the form, right-click the Submit button and click Build Event...
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdSubmit_Click()
        If Not IsDate(txtDateAllocated) Then
            MsgBox "Please enter a valid allocation date, such as the date the loan was approed.", _
                   vbOKOnly, "Watts' A Loan - New Loan Allocation"
            Exit Sub
        End If
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "Please enter the employee number of the clerk who processed or approved the loan.", _
                   vbOKOnly, "Fun Department Store - New Loan Allocation"
            Exit Sub
        End If
        
        If IsNull(cbxLoansTypes) Then
            MsgBox "Please specify the type of the loan that was processed.", _
                   vbOKOnly, "Fun Department Store - New Store Item"
            Exit Sub
        End If
        
        DoCmd.RunSQL "INSERT INTO LoansAllocations(DateAllocated, EmployeeNumber, CustomerFirstName, CustomerLastName, " & _
                     "                             LoanType, LoanAmount, InterestRate, Periods, " & _
                     "                             InterestAmount, MonthlyPayment, FutureValue, PaymentStartDate) " & _
                     "VALUES(#" & CDate(txtDateAllocated) & "#, '" & txtEmployeeNumber & "', '" & txtCustomerFirstName & _
                     "', '" & txtCustomerLastName & "', '" & cbxLoansTypes & "', " & CDbl(Nz(txtLoanAmount)) & ", " & _
                     CDbl(Nz(txtInterestRate)) & ", " & CDbl(Nz(txtPeriods)) & ", " & CDbl(Nz(txtInterestAmount)) & ", " & _
                     CDbl(Nz(txtMonthlyPayment)) & ", " & CDbl(Nz(txtFutureValue)) & ", #" & CDate(txtPaymentStartDate) & "#);"
        DoCmd.Close
    End Sub
  5. Return to Microsoft Access
  6. In the Navigation Pane, right-click NewPayment and click Design View
  7. On the form, right-click the Submit button and click Build Event...
  8. In the Choose Builder dialog box, double-click Code Builder and implement as follows:
    Private Sub cmdSubmit_Click()
        If Not IsDate(txtPaymentDate) Then
            MsgBox "Please enter a valid date when the payment was made.", _
                   vbOKOnly, "Watts' A Loan - New Payment"
            Exit Sub
        End If
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "Please enter the employee number of the clerk who processed the payment.", _
                   vbOKOnly, "Fun Department Store - New Store Item"
            Exit Sub
        End If
        
        If IsNull(txtLoanNumber) Then
            MsgBox "You must enter the number of the loan whose payment is being made.", _
                   vbOKOnly, "Fun Department Store - New Store Item"
            Exit Sub
        End If
        
        DoCmd.RunSQL "INSERT INTO Payments(PaymentDate, EmployeeNumber, LoanNumber, AmountPaid, Balance) " & _
                     "VALUES(#" & CDate(txtPaymentDate) & "#, '" & txtEmployeeNumber & "', " & _
                     CLng(Nz(txtLoanNumber)) & ", " & CDbl(Nz(txtAmountPaid)) & ", " & CDbl(Nz(txtBalanceAfterPayment)) & ");"
        
        DoCmd.Close
    End Sub
  9. Return to Microsoft Access and close the form
  10. When asked whether you want to save, click Yes
  11. On the Ribbon, click File and click Open
  12. In the list of files, click Sweet Star Clothiers from the previous lesson
  13. In the Navigation Pane, right-click PayrollPreparation and click Design View
  14. In the Controls section of the Ribbon, click the Text Box and click the Detail section of the Ribbon
  15. While the text box is still selected, in the Property Sheet, change its characteristics as follows:
    Name: txtPayDate
    Format: Long Date
    Text Align: Left
  16. Complete the design of the form to make the new text box and its accompanying label appear like the other controls. Here is an example:

    Sweet Star Clothiers - Payroll Preparation - Form Design

  17. Right-click an unoccupied area of hte form and click Tab Order...
  18. Arrange the Windows controls in the following order: txtPayDate, txtEmployeeNumber, fraPayCategory, txtWorkUnits, txtTimeWorked, and cmdCalculateSalary:

    Tab Order

  19. Click OK
  20. Save the form

Time-Based Data

Introduction

A time-based field is an object that requests or displays time values.

Visually Creating a Time-Based Field

The visually create a field or control for time values, in either the Datasheet View or the Design View, create a Short Text text box. In the bottom side of the Design View, click Input Mask and click its button. In the first page of the wizard, click the desired time option: Long Time, Short Time, or Medium Time:

Continue with the wizard to the Finish page.

If you are usimg the SQL, you can create a text-based field, a DATE or a DATETIME field.

Data Entry on a Time-Based Field

To perform data entry on a time-based field, the user can click the text box and follow the rules of the time values.

In the SQL, to perform data entry on a time-based field, include the date between # and #. Use any of the formulas we reviewed for time values:

#HH:MM#
#HH:MM:SS#
#HH:MM AM/PM#
#HH:MM:SS AM/PM#

Characteristics of, and Operations on, Date/Time Values

Characteristics of Date/Time Values

The characteristics of date and time values we reviewed in Lessons 10 and 11 apply to controls that display such values. This gives you the ability to get or use the day, the month, the year, the hour, the minute, the day, or the weekday of a date.

Practical Learning: Creating a Date

  1. On the Ribbon, click File and click Open
  2. In the list of files, click WattsALoan1 database used earlier
  3. In the Navigation Pane, right-click the NewLoanAllocation form and click Design View
  4. On the form, double-click the Date Allocated text box
  5. In the Property Sheet, click Event and double-click After Update
  6. Click the ellipsis button Ellipsis to switch to Microsoft Visual Basic
  7. Implement the AfterUpdate event of the DateAllocated text box as follows:
    Private Sub txtDateAllocated_AfterUpdate()
        If Not IsDate(txtDateAllocated) Then
            Exit Sub
        End If
        
        ' Consider the date on which this loan was processed.
        ' If the month of this date is December, ...
        If Month(CDate(txtDateAllocated)) = 12 Then
            ' then the payments will start in January of the following year.
            ' That is, create a date that adds 1 year to the currrent year
            ' and use January (month = 1) for the month
            txtPaymentStartDate = DateSerial(Year(CDate(txtDateAllocated)) + 1, 1, 1)
        Else
            ' If the loan was processed and approved before the middle (the 15) of a month,
            ' the payment will start on the 1st of next month
            If Day(CDate(txtDateAllocated)) <= 15 Then
                txtPaymentStartDate = DateSerial(Year(CDate(txtDateAllocated)), Month(CDate(txtDateAllocated)) + 1, 1)
            Else
                ' If the loan was processed and approved after the middle of a month,
                ' the payment will start the 1st of the month after next
                txtPaymentStartDate = DateSerial(Year(CDate(txtDateAllocated)), Month(CDate(txtDateAllocated)) + 2, 1)
            End If
        End If
    End Sub
  8. Close Microsoft Visual Basic and return to Microsoft Access
  9. Close the form
  10. When asked whether you want to save, click Yes

Operations on Date/Time Values

In Lessons 10 and 11, we reviewed all types of operations to perform on dates and times values. All those operations are valid for ontrols that display date or time values.

Practical Learning: Performing Operations on Date Values

  1. The WattsALoan database should still be opened.
    On the Ribbon, click Create and, in the Forms section, click Form Design
  2. In the Property Sheet, change the following characteristics:
    Auto center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
  3. Save the form as TimeSheet
  4. Design the form as follows:

    Watts' A Loan - New Time Sheet

    Control Caption Name Other Properties
    Label Label Atts A Loan    
    Label Label New Time Sheet    
    Text Box Text Box Employee #: txtEmployeeNumber  
    Text Box Text Box Start Date: txtStartDate Format: Long Date
    Text Box Text Box End Date: txtEndDate Format: Long Date
    Text Box Text Box Time Sheet #: txtTimeSheetNumber  
    Label Label Time Recording    
    Line Line      
    Label Label Monday    
    Label Label Tuesday    
    Label Label Wednesday    
    Label Label Thursday    
    Label Label Friday    
    Label Label Saturday    
    Label Label Sunday    
    Label Label Week 1    
    Line Line      
    Label Label Monday lblWeek1Monday  
    Label Label Tuesday lblWeek1Tuesday  
    Label Label Wednesday lblWeek1Wednesday  
    Label Label Thursday lblWeek1Thursday  
    Label Label Friday lblWeek1Friday  
    Label Label Saturday lblWeek1Saturday  
    Label Label Sunday lblWeek1Sunday  
    Label Label Week 1:    
    Text Box Text Box   txtWeek1Monday Format: Fixed
    Text Box Text Box   txtWeek1Tuesday Format: Fixed
    Text Box Text Box   txtWeek1Wednesday Format: Fixed
    Text Box Text Box   txtWeek1Thursday Format: Fixed
    Text Box Text Box   txtWeek1Friday Format: Fixed
    Text Box Text Box   txtWeek1Saturday Format: Fixed
    Text Box Text Box   txtWeek1Sunday Format: Fixed
    Line Line     Border Style: Dashes
    Label Label Monday lblWeek2Monday  
    Label Label Tuesday lblWeek3Tuesday  
    Label Label Wednesday lblWeek4Wednesday  
    Label Label Thursday lblWeek5Thursday  
    Label Label Friday lblWeek6Friday  
    Label Label Saturday lblWeek7Saturday  
    Label Label Sunday lblWeek8Sunday  
    Label Label Week 2:    
    Text Box Text Box   txtWeek2Monday Format: Fixed
    Text Box Text Box   txtWeek2Tuesday Format: Fixed
    Text Box Text Box   txtWeek2Wednesday Format: Fixed
    Text Box Text Box   txtWeek2Thursday Format: Fixed
    Text Box Text Box   txtWeek2Friday Format: Fixed
    Text Box Text Box   txtWeek2Saturday Format: Fixed
    Text Box Text Box   txtWeek2Sunday Format: Fixed
    Button Button Submit Time Sheet cmdSubmitTimeSheet  
    Button Button Close cmdClose  
  5. Save the form
  6. On the form, double-click the Start Date text box
  7. In the Property Sheet, click Event and double-click On Lost Focus
  8. Click its ellipsis button Ellipsis
  9. Implement the event as follows:
    Private Sub txtStartDate_LostFocus()
        If IsNull(txtStartDate) Then
            Exit Sub
        End If
        
            ' After the user has entered a start date,
        ' get that date
        If Not IsNull(txtStartDate) Then
            ' Add 14 days to the start date to get the end date
            txtEndDate = DateAdd("d", 13, CDate(txtStartDate))
            
            lblWeek1Monday.Caption = txtStartDate
            lblWeek1Tuesday.Caption = DateAdd("d", 1, CDate(txtStartDate))
            lblWeek1Wednesday.Caption = DateAdd("d", 2, CDate(txtStartDate))
            lblWeek1Thursday.Caption = DateAdd("d", 3, CDate(txtStartDate))
            lblWeek1Friday.Caption = DateAdd("d", 4, CDate(txtStartDate))
            lblWeek1Saturday.Caption = DateAdd("d", 5, CDate(txtStartDate))
            lblWeek1Sunday.Caption = DateAdd("d", 6, CDate(txtStartDate))
            
            lblWeek2Monday.Caption = DateAdd("d", 1, CDate(lblWeek1Sunday.Caption))
            lblWeek2Tuesday.Caption = DateAdd("d", 1, CDate(lblWeek2Monday.Caption))
            lblWeek2Wednesday.Caption = DateAdd("d", 1, CDate(lblWeek2Tuesday.Caption))
            lblWeek2Thursday.Caption = DateAdd("d", 1, CDate(lblWeek2Wednesday.Caption))
            lblWeek2Friday.Caption = DateAdd("d", 1, CDate(lblWeek2Thursday.Caption))
            lblWeek2Saturday.Caption = DateAdd("d", 1, CDate(lblWeek2Friday.Caption))
            lblWeek2Sunday.Caption = DateAdd("d", 1, CDate(lblWeek2Saturday.Caption))
        Else
            ' If the start date is empty, don't do anything
            Exit Sub
        End If
    End Sub
  10. Close Microsoft Visual Basic and return to Microsoft Access
  11. Close the form
  12. When asked whether you want to save, click Yes
  13. On the Ribbon, click Create and click Form Design
  14. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Watts' A Loan - Employee Payroll
    Auto Center: Yes
    Border Style: Dialog
    Record Selectors: No
    Navigation Buttons: No
  15. Save the form as Payroll
  16. Right-click the body of the form and click Form Header/Footer
  17. Design the form as follows:

    Watts' A Loan - New Payroll - Form Design

    Control Caption Name Other Properties
    Label Label Watts A Loan    
    Line Line      
    Label Label Employee Payroll    
    Line Line     Border Width: 3 pt
    Line Line      
    Label Label Payroll Identification   Back Color: Background 1, Darker 15%
    Text Box Text Box Employee #: txtEmployeeNumber  
    Text Box Text Box   txtEmployeeName  
    Text Box Text Box Start Date: txtStartDate Format: Long Date 
    Button Button Find Time Sheet cmdFindTimeSheet  
    Text Box Text Box End Date: txtEndDate Format: Long Date 
    Text Box Text Box Pay Date: txtPayDate Format: Long Date 
    Line Line     Border Width: 3 pt
    Line Line      
    Label Label Gross Pay Calculation    
    Label Label Time    
    Label Label Pay     
    Text Box Text Box Regular: txtRegularTime  
    Text Box Text Box   txtRegularPay  
    Text Box Text Box Hourly Salary: txtHourlySalary  
    Text Box Text Box Overtime: txtOvertime  
    Text Box Text Box   txtOvertimePay  
    Text Box Text Box Gross Pay txtGrossPay  
    Line Line     Border Width: 3 pt
    Line Line      
    Label Label Deductions    
    Text Box Text Box Federal Withholding Tax: txtFederalWithholdingTax  
    Text Box Text Box Social Security Tax: txtSocialSecurityTax  
    Text Box Text Box Medicare Tax: txtMedicareTax  
    Text Box Text Box State Tax: txtStateTax   
    Text Box Text Box Net Pay: txtNetPay  
    Line Line      
    Button Button Approve and Submit Payroll cmdApproveSubmitPayroll  
    Button Button Close cmdClose  
  18. On the form, click the txtStartDate text box
  19. In the Property Sheet, double-click On Lost Focus and click its ellipsis button Browse
  20. Implement the event as follows:
    Private Sub txtStartDate_LostFocus()
        txtEndDate = DateAdd("d", 13, CDate(txtStartDate))
        txtPayDate = DateAdd("d", 18, CDate(txtStartDate))
    End Sub
  21. Return to Microsoft Access and close the form
  22. When asked whether you want to save the form, click Yes
  23. On the Ribbon, click File and click open
  24. In the list of files, click Whirl Water Shine from the previous lesson
  25. On the Ribbon, click Create and click Query Design
  26. In the Show Table dialog box, click Close
  27. Right-click the body of the window and click SQL View
  28. Replace the existing code with the following:
    CREATE TABLE WaterBills
    (
        BillNumber        AUTOINCREMENT(100001),
        AccountNumber     TEXT,
        ServiceFromDate   DATE,
        ServiceToDate     DATETIME,
        NumberOfDays      SHORT,
        MeterReadingStart DOUBLE,
        MeterReadingEnd   Double,
        TotalHCF          REAL,
        TotalGallons      LONG,
        First15HCF        Double,
        Next10HCF         FLOAT,
        RemainingHCF      double,
        WaterUsageCharge  Double,
        SewerCharge       Double,
        StormCharge       Double,
        TotalCharges      Double,
        CountyTaxes       Double,
        StateTaxes        number,
        PaymentDueDate    Date,
        AmountDue         double,
        LatePaymentDate   DATETIME,
        LatePaymentAmount double
    );
  29. To execute, on the Ribbon, click the Run button
  30. In the Navigation Pane, right-click WaterBills and click Design View
  31. Change the fields as follows:
    Field Name Field Size Format Caption
    BillNumber     Bill #
    AccountNumber 20   Account #
    ServiceFromDate   Medium Date Service From (Date)
    ServiceToDate   Medium Date Service To (Date)
    NumberOfDays     Number of Days
    MeterReadingStart     Meter Reading Start
    MeterReadingEnd     Meter Reading End
    TotalHCF   Fixed Total HCF
    TotalGallons   Fixed Total Gallons
    First15HCF   Fixed 1st 15 HCF
    Next10HCF   Fixed Next 10 HCF
    RemainingHCF   Fixed Remaining HCF
    WaterUsageCharge   Fixed Water Usage Charge
    SewerCharge   Fixed Sewer Charge
    StormCharge   Fixed Storm Charge
    CountyTaxes   Fixed County Taxes
    StateTaxes   Fixed State Taxes
    PaymentDueDate     Payment Due Date
    AmountDue   Fixed Amount Due
    LatePaymentDate     Late Payment Date
    LatePaymentAmount   Fixed Late Payment Amount
  32. Save and close the table
  33. In the Navigation Pane, right-click Bill Preparation and click Design View
  34. From the Controls section of the Ribbon, add 4 new text boxes to the form as follows:

    Gas Utility Company - Customer Invoice

    Control Caption Name Other Properties
    Text Box Text Box Service From (Date): txtServiceFromDate Medium Date
    Text Box Text Box Service To: txtServiceToDate Medium Date
    Text Box Text Box Payment Due Date: txtPaymentDueDate Medium Date
    Text Box Text Box Late Payment
    Due Date:
    txtLatePaymentDate Medium Date
  35. On the form, click the txtServiceToDate text box
  36. In the Property Sheet, double-click On Lost Focus then click its ellipsis button Browse
  37. Implement the event as follows:
    Private Sub txtServiceToDate_LostFocus()
        If IsNull(txtServiceFromDate) Then
            Exit Sub
        End If
        
        If IsNull(txtServiceToDate) Then
            Exit Sub
        End If
        
        txtNumberOfDays = DateDiff("d", CDate(txtServiceFromDate), CDate(txtServiceToDate))
        txtPaymentDueDate = DateSerial(Year(CDate(txtServiceToDate)), Month(CDate(txtServiceToDate)) + 1, 28)
        txtLatePaymentDate = DateSerial(Year(CDate(txtServiceToDate)), Month(CDate(txtServiceToDate)) + 2, 5)
    End Sub
  38. Close Microsoft Access

Previous Copyright © 2005-2022, FunctionX, Inc. Next