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:
Database: Watts' A LoanWe 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 Learning: Introducing Date-Based Fields
Control | Caption | Name | Other Properties | |
Label | Watts' A Loan | |||
Label | Loan Evaluation | |||
Text Box | Loan Amount: | txtLoanAmount | ||
Text Box | Interest Rate: | txtInterestRate | ||
Label | % | |||
Text Box | Periods | txtPeriods | ||
Label | Months | |||
Button | Calculate | cmdCalculate | ||
Line | ||||
Text Box | Periodic Payment: | txtPeriodicPayment | ||
Label | /Month | |||
Text Box | Interest Amount: | txtInterestAmount | ||
Text Box | Future Value: | txtFutureValue | ||
Button | Close | cmdClose |
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
Private Sub cmdClose_Click() DoCmd.Close End Sub
Control | Name | Caption | |
Label | Tables | ||
Label | Records | ||
Line | |||
Button | cmdLoansAllocations | Loans Allocations | |
Button | cmdLoanAllocation | Loan Allocation . . . | |
Button | cmdPayments | Payments | |
Button | cmdPayment | Payment . . . | |
Button | cmdLoansTypes | Loans Types | |
Button | cmdEmployeesRecords | Employees... | |
Button | cmdTimeSheets | Time Sheets | |
Button | cmdNewTimeSheet | New Time Sheet . . . | |
Button | cmdPayrolls | Payrolls | |
Button | cmdPayroll | Payroll . . . | |
Button | cmdEmployeesTable | Employees | |
Button | cmdClose | Close |
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
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
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
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
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
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
Private Sub cmdClose_Click() DoCmd.Close End Sub
Control | Caption | Name | Other Properties | |
Label | Watts' A Loan | |||
Label | New Loan Allocation | |||
Label | Processed By | Back Color: #727272 | ||
Text Box | Employee #: | txtEmployeeNumber | ||
Label | Processed For Customer | Back Color: #727272 | ||
Text Box | First Name:: | txtCustomerFirstName | ||
Text Box | Last Name: | txtCustomerLastName | ||
Line | Border Width: 2 pt Border Color: Dark Gray 5 |
|||
Text Box | Loan Type: | cbxLoansTypes | ||
Text Box | Loan Amount: | txtLoanAmount | ||
Text Box | Interest Rate: | txtInterestRate | ||
Text Box | Periods (Months): | txtPeriods | ||
Button | Evaluate Loan | cmdEvaluateLoan | ||
Line | Border Width: 2 pt Border Color: Dark Gray 5 |
|||
Text Box | Interest Amount: | txtInterestAmount | ||
Text Box | Future Value: | txtFutureValue | ||
Text Box | Monthly Payment: | txtMonthlyPayment | ||
Button | Submit | cmdSubmit | ||
Button | Close | cmdClose |
Private Sub cmdEvaluateLoan_Click() DoCmd.OpenForm "LoanEvaluation" End Sub
Control | Caption | Name | Other Properties | |
Label | Watts' A Loan | |||
Label | New Payment | |||
Label | Payment Processed By | Back Color: #727272 | ||
Text Box | Employee #: | txtEmployeeNumber | ||
Label | Payment Processed For | Back Color: #727272 | ||
Text Box | Loan Number:: | txtLoanNumber | ||
Text Box | txtLoanDetails | |||
Text Box | Amount Paid: | txtAmountPaid | ||
Label | Balance | Back Color: #727272 | ||
Text Box | Before Payment: | txtBalanceBeforePayment | ||
Text Box | After Payment: | txtBalanceAfterPayment | ||
Button | Submit | cmdSubmit | ||
Button | Close | cmdClose |
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: . 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):
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
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
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
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
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
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
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
Control | Caption | Name | Other Properties | |
Label | Atts A Loan | |||
Label | New Time Sheet | |||
Text Box | Employee #: | txtEmployeeNumber | ||
Text Box | Start Date: | txtStartDate | Format: Long Date | |
Text Box | End Date: | txtEndDate | Format: Long Date | |
Text Box | Time Sheet #: | txtTimeSheetNumber | ||
Label | Time Recording | |||
Line | ||||
Label | Monday | |||
Label | Tuesday | |||
Label | Wednesday | |||
Label | Thursday | |||
Label | Friday | |||
Label | Saturday | |||
Label | Sunday | |||
Label | Week 1 | |||
Line | ||||
Label | Monday | lblWeek1Monday | ||
Label | Tuesday | lblWeek1Tuesday | ||
Label | Wednesday | lblWeek1Wednesday | ||
Label | Thursday | lblWeek1Thursday | ||
Label | Friday | lblWeek1Friday | ||
Label | Saturday | lblWeek1Saturday | ||
Label | Sunday | lblWeek1Sunday | ||
Label | Week 1: | |||
Text Box | txtWeek1Monday | Format: Fixed | ||
Text Box | txtWeek1Tuesday | Format: Fixed | ||
Text Box | txtWeek1Wednesday | Format: Fixed | ||
Text Box | txtWeek1Thursday | Format: Fixed | ||
Text Box | txtWeek1Friday | Format: Fixed | ||
Text Box | txtWeek1Saturday | Format: Fixed | ||
Text Box | txtWeek1Sunday | Format: Fixed | ||
Line | Border Style: Dashes | |||
Label | Monday | lblWeek2Monday | ||
Label | Tuesday | lblWeek3Tuesday | ||
Label | Wednesday | lblWeek4Wednesday | ||
Label | Thursday | lblWeek5Thursday | ||
Label | Friday | lblWeek6Friday | ||
Label | Saturday | lblWeek7Saturday | ||
Label | Sunday | lblWeek8Sunday | ||
Label | Week 2: | |||
Text Box | txtWeek2Monday | Format: Fixed | ||
Text Box | txtWeek2Tuesday | Format: Fixed | ||
Text Box | txtWeek2Wednesday | Format: Fixed | ||
Text Box | txtWeek2Thursday | Format: Fixed | ||
Text Box | txtWeek2Friday | Format: Fixed | ||
Text Box | txtWeek2Saturday | Format: Fixed | ||
Text Box | txtWeek2Sunday | Format: Fixed | ||
Button | Submit Time Sheet | cmdSubmitTimeSheet | ||
Button | Close | cmdClose |
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
Control | Caption | Name | Other Properties | |
Label | Watts A Loan | |||
Line | ||||
Label | Employee Payroll | |||
Line | Border Width: 3 pt | |||
Line | ||||
Label | Payroll Identification | Back Color: Background 1, Darker 15% | ||
Text Box | Employee #: | txtEmployeeNumber | ||
Text Box | txtEmployeeName | |||
Text Box | Start Date: | txtStartDate | Format: Long Date | |
Button | Find Time Sheet | cmdFindTimeSheet | ||
Text Box | End Date: | txtEndDate | Format: Long Date | |
Text Box | Pay Date: | txtPayDate | Format: Long Date | |
Line | Border Width: 3 pt | |||
Line | ||||
Label | Gross Pay Calculation | |||
Label | Time | |||
Label | Pay | |||
Text Box | Regular: | txtRegularTime | ||
Text Box | txtRegularPay | |||
Text Box | Hourly Salary: | txtHourlySalary | ||
Text Box | Overtime: | txtOvertime | ||
Text Box | txtOvertimePay | |||
Text Box | Gross Pay | txtGrossPay | ||
Line | Border Width: 3 pt | |||
Line | ||||
Label | Deductions | |||
Text Box | Federal Withholding Tax: | txtFederalWithholdingTax | ||
Text Box | Social Security Tax: | txtSocialSecurityTax | ||
Text Box | Medicare Tax: | txtMedicareTax | ||
Text Box | State Tax: | txtStateTax | ||
Text Box | Net Pay: | txtNetPay | ||
Line | ||||
Button | Approve and Submit Payroll | cmdApproveSubmitPayroll | ||
Button | Close | cmdClose |
Private Sub txtStartDate_LostFocus() txtEndDate = DateAdd("d", 13, CDate(txtStartDate)) txtPayDate = DateAdd("d", 18, CDate(txtStartDate)) End Sub
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 );
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 |
Control | Caption | Name | Other Properties | |
Text Box | Service From (Date): | txtServiceFromDate | Medium Date | |
Text Box | Service To: | txtServiceToDate | Medium Date | |
Text Box | Payment Due Date: | txtPaymentDueDate | Medium Date | |
Text Box | Late Payment Due Date: |
txtLatePaymentDate | Medium Date |
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
|
||
Previous | Copyright © 2005-2022, FunctionX, Inc. | Next |
|