Fundamentals of Dates

Introduction to Dates

A date is a non-spatial measure of the number of days that have occurred in a period. When a certain number of days, namely 28, 29, 30, or 31 (depending on some factors) is reached, the measure is called a month. When 12 months have occurred, the measure is called a year (after 10 years have occurred, the measure is called a decade; after 100 years have occurred, the measure is called a century. After 1000 years have occurred, the measure is called a millennium; but the decade, the century, and the millennium are hardly used in calculations).

Practical Learning: Introducing Date Values

1. Start Microsoft Access
2. In the list of files, click FunDS1 from the Lesson 20
3. In the Navigation Pane, right-click the StoreItems form and click Design View
4. On the form, click the text box below Date in Store and press Delete
5. Click the text box below Today and press Delete
6. Close the form
7. When asked whether you want to save, click Yes

A Field that Supports Date Values

By default, to display date values in a database, Microsoft Access uses some letters and characters. As mentioned for times, the rules for dates are built in the operating system and you can check them in the computer:

To support date values, Microsoft Access internally uses a data type named Date. In a table, this data type is referred to as Date/Time. Over all, dates and times are considered differently but, to specify that a field would use date, time, or both, set its Data Type to Date/Time. You can specify this data type for a field what creating a table either in the Datasheet View or in the Design View.

To create a field that receives or displays date values:

• In the Datasheet View, click Fields on the Ribbon. Then:
  • Click a cell under Click to Add. In the Add & Delete section of the Ribbon, click the Date & Time button
  • Click Click to Add and select Date & Time from the list

• In the Design View, after specifying the name of the field, specify its Data Type as Date/Time

Practical Learning: Introducing the Date Type

1. In the Navigation Pane, double-click the StoreItems table
2. On the table, click any cell under Click to Add
3. On the Ribbon, click Fields
4. In the Add & Delete section, click the Date & Time button
5. Type DateInStore and press Enter
6. Click any cell under DateInStore
7. In the Properties section of the Ribbon, click Name & Caption
8. In the Caption, type Date in Store
9. In the Description, type The first date the item was displayed in the store
10. Click OK
11. Right-click the title bar of the table and click Design View
12. Click the box on the left side of DateInStore

    

13. To move the column, click the row header again and hold your mouse on it
14. Drag the selection up and position it below ItemNumber

    

15. Release the mouse

    

16. Close the table
17. When asked whether you want to save the table, click Yes

Date Values in SQL

To support date values, the SQL provides a data type named DATETIME. It is equivalent to Microsoft Access Date/Time data type.

The Value of a Date

In Microsoft Access, the value of a date starts and ends with #. An example is:

#6/2/2018#

Introduction to the Formats of a Date Value

As stated already, every language or system provides some standard formats for date values. You can indicate the format a new field should use or you can change the format of an existing field.

When creating a new field, to directly specify its format, 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:

• In the Datasheet View of a table, click a cell in the column. On the Ribbon, click Fields. In the Formatting section, select the desired option in the Format combo box
• In the Design View of a table, select the field in the top part of the window. In the bottom part, click Format and select the desired option in the combo box
• In the Design View of a form or report, access the Property Sheet of the text box. Click Format or All. In the Format combo box, select the desired option

Practical Learning: Introducing Date Formats

1. In the Navigation Pane, double-click the ShoppingSessions table
2. On the table, click any cell in the Employee # column
3. On the Ribbon, click Fields
4. In the Add & Delete section, click More Fields and, in the Date and Time section, click Long Date
5. Type ShoppingDate and press Enter
6. Click a cell under ShoppingDate
7. In the Properties section of the Ribbon, click Name & Caption
8. In the Caption, type Shopping Date
9. In the Description, type The date a customer bought the merchandise
10. Click OK
11. Close the table
12. In the Navigation Pane, double-click the StoreItems table
13. On the table, click any cell in the Date in Store column
14. On the Ribbon, click Fields
15. In the Formatting section of the Ribbon, click the arrow of the Format combo box and select Short Date
16. Close the table
17. In the Navigation Pane, right-click the StoreItems form and click Design View
18. In the Tools section of the Ribbon, click Add Existing Fields
19. In the Field List, drag DateInStore and drop it in the empty area below the Date in Store labe
20. Right-click the label that was added and click Cut
21. Format the text box to appear like the other controls (font, color, border color)

    

22. Close the form
23. When asked whether you want to save, click Yes
24. In the Navigation Pane, right-click the ShoppingSessions form and click Design View
25. In the Tools section of the Ribbon, click Add Existing Fields
26. In the Field List, drag ShoppingDate and drop it in an empty area in the Detail section
27. Format the label and the text box to appear like the other controls (font, color, border color)

    

28. Close the form
29. When asked whether you want to save, click Yes
30. In the Navigation Pane, right-click the SoldItems table and click Design View
31. Click the first empty under Field Name, type PurchasePrice and press Tab
32. Click the arrow of the Data Type combo box and select Number
33. In the bottom side of the window, click the arrow of the Field Size combo box and select Double
34. Save the table
35. On the Ribbon, click File and click Open
36. In the list of files, click StatesStatistics1 from Lesson 21
37. In the Navigation Pane, right-click the States table and click Design View
38. In the top part of the window, click the first empty cell under Field Name and type AdmissionUnionDate
39. Set the data type as Date/Time
40. In the bottom part of the window, change the format to Long Date
41. Click Caption and type Date of Admission to the Union
42. Close the table
43. When asked whether you want to save the table, click Yes
44. In the Navigation Pane, right-click the States form and click Design View
45. In the Tools section of the Ribbon, click Add Existing Fields
46. From the Field List, drag AdmissionUnionDate and drop it on the form in the Detail section of the form
47. Format the label to appear like the State or Area label
48. Format the text box to appear like the other text boxes:

    

49. Save the form and switch it to Form View
50. Update the records as follows:
     

    Abbreviation	Date of Admission to the Union
    AK	01/03/1959
    AL	12/14/1819
    AZ	02/14/1912
    AR	06/15/1836
    CA	09/09/1850

    

    

    

51. Close the form
52. In the Navigation Pane, right-click the Summary form and click Design View
53. In the Controls section of the Ribbon, click the Label control and click the Form Header section
54. Type Admission to Union
55. In the Controls section of the Ribbon, click Add Existing Fields
56. From the Field List, drag AdmissionUnionDate and drop it on the form in the Detail section
57. Right-click the label that was added and click Cut
58. Right-click the Form Header section and click Paste
59. Move that label below the Admission to Union label
60. Format the label to appear like the other labels
61. Format the text box to appear like the other text boxes:

    

62. Save and close the form

Details on Dates Masks

If the regular formats don't satisfy your needs, you can use use a custom format created using some characters and symbols but based on some rules. A summary of the letters used and their combinations are as follows:

To use these letters, you can simply type the desired combination in the Format field of the Design View of the table or the Format combo box of the Property Sheet of the text box of a form or report. Here is an example:

This is configured to display a single digit for a day of the month if the day is less than 10, followed by the complete name of the month, followed by the year in 4 digits.

During data entry, the user can enter a valid date. Once the field looses focus, it displays the date based on the format. Based on this, you can use any combination of formats but you should use a combination most regularly used in your language so the users would not be confused.

Besides the indicated characters, you may want to use some other symbols to separate them. An example would be January 5, 2024, which uses a comma in the display. To create such sections, include the characters in double-quotes. What you would be doing is to ask Microsoft Access to display such characters "as is" while considering the non-quotes characters as part of the format. Here is an example:

Practical Learning: Using Date Masks

1. The StatesStatistics database should still be opened.
   In the Navigation Pane, right-click the Summary form and click Design View
2. In the Tools section of the Ribbon, click Add Existing Fields
3. Drag AdmissionUnionDate and drop it in the Detail section of the Ribbon
4. Move the label to the Form Header section and change to caption to Admission to Union
5. Right-click the text box and click Properties
6. Click the Format tab
7. Click Format and type dd-mmm-yyyy
8. Format both the label and the text box to appear like the controls in the same sections

   

9. Save and close the form

Dates Masks

In the Design View of a table, the Format property allows you to specify how a date would display in a field but not how the user must enter it. To specify how the date must be typed in a field or control, you can use the Input Mask property. You can either type the mask or you can click the ellipsis button and follow the wizard. If you want to type your mask, you can use an appropriate combination of the letters and symbols we saw above.

Data and Date-Based Fields

Data Entry on a Date-Based Field

Microsoft Access provides two techniques to assist the user in specifying the value of a date-based field. To make data entry easy, after you have 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 calendar appears on the right side of the field or the text box when the object receives focus. The user can then click the calendar and select the desired date. The calendar completely reduces the likelihood of a mistake.

As an alternative to the calendar, the user can manually type the value of the date. In this case, the user must respect the rules of both the format that was set and the language that is being used. This means that the value to enter depends on the format that was set, if any. The value also depends on the universal rules of date. For example, the month of January cannot have more than 31 days and the month of February can have 29 days only if the specified year is leap.

If no format was set, after entering the value, the user can press Enter or Tab. At this time, Microsoft Access would analyze the value the user entered. If the value is not valid, Microsoft Access would display an error and reject the value. This is one of the fundamental differences between a text-based field and a date-oriented column. In a text-based field, the user can enter any date, including one that is not valid. You would find out only when you try to use that value in an operation.

Practical Learning: Adding Date-Based Fields

1. On the Ribbon, click File and click Open
2. In the list of files, click FunDs1
3. In the Navigation Pane, double the StoreItems form
4. Click a text box under Date in Store and notice the calendar button on the right
5. Using the calendar, update the following records:
    

   Item #	Date in Store
   290699	April 28, 2016
   379367	April 27, 2016
   746827	May 23, 2016
   388729	May 27, 2016
   668156	May 30, 2016
   345500	May 21, 2016
   430669	May 24, 2016
   426643	May 16, 2016
   136843	February 2. 2016
   366154	May 18, 2016
   735312	May 18, 2016
   388663	April 18, 2016
   441180	June 1, 2016
   211771	April 18, 2016
   545127	May 16, 2016
   239679	May 4, 2016

6. Close the form
7. In the Navigation Pane, double-click the ShoppingSessions table
8. Click a cell under Shopping Date and notice the calendar button
9. Update the records as follows:
    

   Receipt #	Shopping Date
   100001	June 1, 2016
   100002	June 1, 2016
   100003	June 2, 2016
   100004	June 2, 2016
   100005	June 2, 2016
   100006	June 4, 2016

10. Close the form
11. In the Navigation Pane, double-click the ShoppingSessions form

    

    

12. Close the form

Converting a Value to Date

Before involving a value or an expression in a date-based operation, you should first convert it. If the values of a table field are provided as strings, the primary way to convert them is to change the data type of the column to a date-based type. If all values are valid,  the conversion would be smooth. If at least one of the values is not valid, the conversion would fail and you would receive an error.

To support value conversion to date, Microsoft Access provides a function named CDate. Its syntax is:

CDate(expression) As Date

The Components of a Date Value

This function takes a value, an expression, or the name of a field as argument and analyze it. The value of the argument must follow the rules of dates specified by both the universal conventions and the language being used (such as US English). If the argument holds a valid date, the function returns that date. If the value is not valid, the function returns an error.

Introduction

We saw that a date was made of the day, the month, and the year. On an existing date, you can get these values by calling their corresponding functions.

The Day, the Month, and the Year Parts of a Date

To let you get the year part of a date, Microsoft Access provides a function named Year. Its syntax is:

Year(expression) As Integer

This function takes a date as argument. If the argument is a valid date, the function produces the year as a number.

To get the month of a date, call a function named Month. Its syntax is:

Month(expression) As Integer

To get the day of a date, call a function named Day. Its syntax is:

Day(expression) As Integer

Practical Learning: Accessing the Year of a Date

1. On the Ribbon, click File and click Open
2. In the list of files, click StatesStatistics1
3. In the Navigation Pane, right-click the Summary form and click Design View
4. In the Controls section of the Ribbon, click the Text Box control and click the Detail section of the Ribbon
5. Right-click the label that was added and click Cut
6. Right-click the Form Header section and click Paste
7. Click the text box that was added
8. In the Property Sheet, click the All tab, then click Name and type txtAdmissionUnionYear
9. Click Control Source and click its ellipsis button
10. As the caret is positioned in the text box, type Year(

    

    

11. In the middle list box, double-click AdmissionUnionDate
12. Type )

    

13. Click OK
14. Change the caption of the label to Year
15. Format the label and the text box to appear like the other controls:

    

16. Right-click an empty area of the form and click Tab Order
17. In the Tab Order dialog box, click Auto Order
18. Click OK
19. In the Views section of the Ribbon, click the View button to see the records
20. Close the form
21. When asked whether you want to save, click Yes

The Week in a Date

The week is a combination of 7 days that consecutively occur in a date. To identify them, each day has a specific index and a specific name. In US English, the names of the days are Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday. In some cases, the days may be counted from Sunday (instead of Monday). The option to specify what is considered as the first day of the week is referred to as firstdayofweek and is represented in Microsoft Access by one of the following values:

In most operations that involve date values, you will consider that the first day of the week is Sunday, represented with 1. In some cases, if you want to use another day as the starting point of a week, you can specify it from one of the above values.

To let you get the name of a week, Microsoft Access provides the WeekdayName() function. Its syntax is:

WeekdayName(weekday As Integer,
	    Option As Boolean,
	    Optional firstdayofweek As Integer) As String

This function takes three arguments with only the first required. The first argument is passed as an integer and can be one of the above values. The second argument is optional and it specifies whether the weekday name will be abbreviated. The third argument, firstdayofweek, also optional, specifies what day should be considered as the first day of the week. It can be one of the above numbers.

To get the index (or position) of a day of a week from a date value, you can call the Weekday() function. Its syntax is:

Weekday(expression, Optional firstdayofweek As Integer) as Integer

The first argument is required. It can be an expression or a control that must produce a date. If the function succeeds, it produces a number that represents the day of the week.

The Current Date

To let you know the current date on a computer, Microsoft Access provides a function named Date.

Practical Learning: Getting the Current Date

1. In the Navigation Pane, right-click the StoreItems form and click Design View
2. In the Controls section of the Ribbon, click the Text Box control and click the empty area below Today in the Detail section of the form
3. Right-click the label that was added and click Cut
4. Using the Property Sheet, change the characteristics of the text box as follows:
   Name: txtToday
   Control Source: =Date()
5. Format the text box to appear like the others:

   

6. Save the form

Operations on Date Values

Finding the Difference Between Two Date Values

To let you find the difference between two date values, Microsoft Access provides a function named DateDiff. It allows you to find the number of days, weeks, months, or years from two date values. The DateDiff() function takes 5 arguments, 3 are required and 2 are optional. Its syntax is:

DateDiff(interval, date1, date2, option1, option2)

The interval argument is required and it specifies the kind of value you want as a result. This argument is passed as a string and can have one of the following values:

The date1 and date2 arguments are required. They specify the date values for the operation.

By default, the days of a week are counted starting on Sunday. If you want to start counting from another day, pass the option1 argument using one of the following values: 1, 2, 3, 4, 5, 6, or 7 (there are other variances to that argument).

If your calculation involves weeks, or if you are trying to find the number of weeks, by default, the weeks are counted starting January 1st. If you want to count your weeks starting at a different date, use the option2 argument to specify where the program should start.

Practical Learning: Finding the Difference Between Two Date Values

1. The StoreItems form should still be opened in Design View.
   On the form, click the text box below Days in Store
2. In the Property Sheet, click the All tab, click Control Source and click its ellispsis button
3. In the text box, type =DateDiff("d",

   

4. In the middle list, double-click DateInStore
5. Type a comma
6. In the middle list, double-click txtToday
7. Type )

   

8. Click OK

   

9. Switch the form to Form View to view the records

   

10. Close the form
11. When asked whether you want to save, click Yes

Adding a Number to a Date Value

To let you add a value to a date, Microsoft Access provides a function named DateAdd. It is used to add a number of days, weeks, months, or years to a date. The syntax of the DateAdd() function is:

DateAdd(interval, Number, date) As Date

The interval argument is required and it specifies the kind of value needed as a result. This argument can be passed as a string (in double-quotes). It can have one of the following values:

The number argument is required. It specifies the number of units you want to add or subtract. If you set it as positive, its value will be added. If you want to subtract, pass it as a negative value.

The date argument is the date to which you want to add the number.

Fundamentals of Times

Introduction to Time Values

A time is a non-spatial measure of the number of seconds that have elapsed in a set period. Starting at a specified period and counting incrementally, if the number of seconds reaches 60, the measure is called a minute. If the number of minutes reaches 60, the measure is called an hour. If 24 measures of hours occur, the measure is called a day (actually a day is 24 hours plus a few more seconds but the seconds are taken into consideration only every 4 years).

Time Values in SQL

The SQL support time values with the same DATETIME data type used for dates.

The Time Value

To create a time value, use an appropriate combination of the above letters and characters. In most cases, you should include the combination between two # signs. An example would be:

#05:42#

This would represent 5:42 AM. Another example is:

#10:26 AM#

This also represents a time in the morning. Remember that you can also include the seconds in your time value.

Time Rules

By default, to display or handle time values in a database, Microsoft Access uses some letters and characters. The default rules are defined in the computer:

To support time values, Microsoft Access uses the same Date data type reviewed for dates. If you are creating a field in a table, to make it hold time values, if you are working in the Datasheet View, apply the Date & Time type as we reviewed for the date. If you are working in the Design View of the table, specify the data type of the field as Date/Time.

Time Formats

You can specify how the time values would appear on a field or the corresponding text box. To do this, after creating a field:

• In the Datasheet View, on the Ribbon, click Fields:
  • To create a new field that supports a certain format, either click a cell under Click to Add or click a cell under the column that will precede the new column. In the Add & Delete section of the Ribbon, click More Fields. In the Date and Time section, click the desire time option: Time am/pm (same as Long Time), Medium Time, or Time 24hour (same as Short Time)
  • To specify or change the format, click a cell under the column header. In the Formatting section, select the desired option in the Format combo box as Long Time, Medium Time, or Short Time
• In the Design View of a table, in the top section, click the name of the Date/Time field. In the lower section, click Format and select the desired option among Long Time, Medium Time, and Short Time
• In the Design View of a form or report, access the Property Sheet of the text box. In the Format or the All tab, in the Format combo box, select the desired option as Long Time, Medium Time, and Short Time

Practical Learning: Creating a Time-Based Field

1. The FunDS1 database should still be opened.
   In the Navigation Pane, double-click the ShoppingSessions table
2. On the table, click any cell under Shopping Date
3. On the Ribbon, click Fields
4. In the Add & Delete section, click More Fields and click Time am/pm
5. Type ShoppingTime and press Enter
6. On the table, click any cell under ShoppingTime
7. On the Ribbon, click Name & Caption
8. Set the caption as Shopping Time
9. Set the Description as The time the purchase was completed
10. Click OK
11. Update the records as follows:
     

    Receipt #	Shopping Time
    100001	10:24
    100002	16:38
    100003	20:05
    100004	13:12
    100005	12:46
    100006	14:26

12. Close the table
13. When asked whether you want to save, click Yes
14. In the Navigation Pane, right-click the ShoppingSessions form and click Design View
15. In the Tools section of the Ribbon, click Add Existing Fields
16. From the Field List, drag ShoppingTime and drop it on the form
17. Format the label and the text box to appear like the other controls:

    

18. Switch the form to Form View

    

    

    

19. Close the form

Time Masks

The Format property in the Design View of a table is used to show how the time values should appear in the field. First, the user must enter the value. To assist the user with how to enter the time, you can create a custom mask. To do this, click Input Mask and type a mask. Otherwise, you can click the ellipsis button and follow the wizard.

As stated already, the rules that specify what characters and symbols are used to display time values are in the Time tab of the Customize Regional Options. The characters used for the hours, the minutes, and the seconds are defined in the Time Format combo box:

To control how time values should display in a field, after setting its Data Type to Date/Time, use the Format property. The characters used to create a format are:

When combining these characters to create a format, you should follow the rules of your language. You should refer to the formula set in the Time property page of the Regional (and Language) Settings of Control Panel. Microsoft Access also refers to it for the character separator. If you want to include any other character, type it in double-quotes.

Operations on Time Values

Conversion to a Time Value

Before involving a value in a time-based operation, you should first check and convert it to a recognizable format. To do this, call the same CDate() function we saw for dates. Its syntax is:

CDate(expression) As Time

This function takes one argument that can be passed as the name of a control, as a string that holds a time value, or as an expression that is supposed to produce a time value. If the value passed as argument holds a recognizable time, the function returns that time.

The Components of a Time Value

As mentioned in our introduction, a time value is made of the hours, the minutes, and the seconds. If you already have a time or if you retrieve one from an object or you get one from an expression, you may be interested in only one or its components. There are functions you can use to get these values.

To get the hour value of a time, call a function named Hour. Its syntax is:

Hour(expression) As Integer

To get the minute of a time value, call a function named Minute. Its syntax is:

Minute(expression) As Integer

To get the second value of a time, call a function named Second(). Its syntax is:

Second(expression) As Integer

The Current Time

To give you the current time, Microsoft Access provides a function named Time.

Adding to a Time Value

To add a value to a time, call the same DateAdd() function we reviewed for dates. The DateAdd() function is used to add an interval value to a specific time. With it, you can add a number of seconds, minutes, or hours to another time. As a reminder, the syntax of the DateAdd() function is:

DateAdd(interval, number, date) As Time

The interval argument is passed as a string and can have one of the following values:

The Number argument is required also. It specifies the number of units you want to add. If you set it as positive, its value will be added. If you want to subtract, make it negative.

The date argument is the date to which you want to add the number.

Subtraction From a time

The DateDiff() function is used to find the difference between two time values. It allows you to find the number of seconds, minutes, or hours when you supply two recognizable values. The DateDiff() function takes 5 arguments, 3 are required and 2 are optional.

The syntax of the function is

DateDiff(Interval, Time1, Time2, Option1, Option2) As Time

The Interval argument is required and it specifies the kind of value you want to subtract. This argument is passed as a string and can have one of the following values:

Required also, the Time1 and Time2 arguments specify the time values that will be used when performing the operation.

The Date and Time Value

Introduction

In some cases, you may want to consider both the date and the time as one value. Microsoft Access supports this concept through the same Date/Time data type we have seen so far. In fact, when you set the data type of a field to Date & Time or Date/Time and set the format for a date, you are simply asking Microsoft Access to ignore the time part; otherwise, the time becomes set to midnight.

Practical Learning: Creating a Date/Time Field

1. On the Ribbon, click File and click Open
2. In the list of files, click College Park Auto-Repair1
3. In the Navigation Pane, double-click the RepairOrders table
4. On the table, click a cell below Problem Description
5. On the Ribbon, click Fields
6. In the Add & Delete section, click the Date & Time button
7. Type DropOffDateTime and press Enter
8. Click a cell under DropOffDateTime
9. In the Properties section of the Ribbon, click Name & Caption
10. Set the Caption as Drop off Date/Time
11. Click OK

Date/Time Values in SQL

The SQL DATETIME data type is used for dates, times, or combinations of date and time values.

The Format of a Date/Time Value

A date/time value is a combination of the date and the time. If you want a Date/Time based field to hold both the date and the time values as one, you can set its Format as Long Date. An alternative is to create a mask that is a combination of the dates and the times masks we reviewed in previous sections.

Practical Learning: Applying a Mask to a Date/Time Field

1. Right-click the title bar of the RepairOrders table and click Design View
2. In the top part of the window, click DropOffDateTime
3. In the bottom part of the window, click Format and type dddd ", "mmmm dd", "yyyy" at "hh:nn:ss
4. In the top part of the window, right-click TaxRate and click Insert Rows
5. Type OrderCompletedDateTime
6. In the corresponding Data Type combo box, select Date/Time
7. In the bottom part of the window, change the following characteristics:
   Format: dddd ", "mmmm dd", "yyyy" at "hh:nn
   Caption: Order Completed Date/Time
8. Save and close the table
9. In the Navigation Pane, right-click the RepairOders form and click Design View
10. In the Tools section of the Ribbon, click Add Existing Fields
11. From the Field List, drag DropOffDateTime and drop it in an empty area in the Detail section of the form
12. Drag OrderCompletedDateTime and drop it in an empty area in the Detail section of the form
13. Position the controls below-left of the tab control on the form and format them to appear like the other controls:

    

14. Save the form

Data Entry on a Date/Time Value

When a field or a text box that is used for both the date and the time value is accessed, if you know the date and time combination you want to use, type it between # symbols. An example would be #06/15/2016 10:44. Otherwise, when the field or the text box has focus, it displays a calendar button on the right side. You can use it to specify the value of the date. You should then press the Space bar and type the time value. The field or the text box would then display both values.

The Current Date and Time

To get both the current date and the current time of the computer, call a function named Now.

The Components of a Date and Time Value

Because the date/time value is a combination of date and time, it includes the components we reviewed for the date and those we reviewed for the time. As a result, you can call their functions to get the individual values.

Adding a Value to a Date/Time Value

To add a value to date and time value, you can call the same DateAdd() function used for date or time values. This allows you to add months, days, years, hours, minutes, or seconds. The result would include both the date and the time.

The Difference Between Two Date/Time Values

If you provide only date values to a the DateDiff() function, you will get only the raw days, months, or years. In the same way, a call to the DateDiff() function is suitable only if both times occur on the same day. As a better alternative, the Date data type (actually called a class) allows more precision if you provide a combination of the date and time values for each part.

Practical Learning: Creating Date/Time Values

1. The RepairOrders form should still be opened in Design View.
   In the Controls section of the Ribbon, click the Text Box control and click below the date-based controls that were previously added
2. Change the caption of the accompanying label to Repair Duration:
3. Click the text box to select it
4. In the Property Sheet, click the All tab and change its name to txtRepairDuration
5. Click Control Source and click its ellispsis button
6. In the left list box, click the + button of Functions to expand it and click Built-In Functions
7. In the middle list, click Date/Time
8. In the right list, double-click DateDiff

   

9. In the text box, click <<interval>> to select it and type "n"
10. Click <<date1>> to select it
11. In the left list, click the + button of College Park Auto-Repair1 to expand it and click the + button of Forms to expand it too
12. Expand Loaded Forms and click RepairOrders
13. In the middle list, double-click DropOffDateTime
14. In the text box, click <<date2>> to select it
15. In the middle list, double-click OrderCompletedDateTime
16. In the text box, select , <<firstdayofweek>>, <<firstweekofyear>> and press Delete

    

17. Click OK
18. Format the design of the label and the text box to appear like the other controls:

    

19. From the Controls section of the Ribbon, add 3 text boxes
20. Using the Property Sheet, change their characteristics as follows:
     

    Associated Label - Caption	Text Box Name	Control Source
    Equivalent to:	txtDays	=IIf(DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime])\1440=0,"",IIf(DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime])\1440=1,DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime])\1440 & " day",DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime])\1440 & " days"))
    Delete	txtHours	=IIf((DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime])\60) Mod 24=0,"",IIf((DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime])\60) Mod 24=1,(DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime])\60) Mod 24 & " hour",(DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime])\60) Mod 24 & " hours"))
    Delete	txtMinutes	=IIf(DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime]) Mod 60=0,"",IIf(DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime]) Mod 60=1,DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime]) Mod 60 & " minute",DateDiff("n",[DropOffDateTime],[OrderCompletedDateTime]) Mod 60 & " minutes"))

21. Format the controls to appear like the others

    

22. Switch the form to Form View to view the records
23. Close the form
24. When asked whether you want to save, click Yes
25. In the Navigation Pane, double-click the RepairOrders form to open it in Form View
26. Click the Drop Off Date/Time text box and click its calendar button
27. In the calendar, select the date for June 15, 2016
28. Press the Space bar, type 10:26 and press Tab
29. Fill out the following pieces of information:
    Customer Name: Laura Gladd
    Make: Toyota
    Model: Corolla
    Year: 2006
30. Click the Problem Description tab and, in the text box, type The car needs tune up
31. Click the Jobs Performed tab
32. Click the first empty text box below Job Performed and type The spark plugs were replaced
33. Press Tab and, in the corresponding Cost text box, type 0.00
34. Click the second (empty) text box below Job Performed and type The air and the fuel filters were replaced
35. Press Tab and, in the corresponding Cost text box, type 155.75
36. Click the Parts Used tab
37. Click the first empty text box below Part Name and type V-Power Spark Plug
38. Press Tab and, in the corresponding Unit Price box, type 3.74
39. Press Tab and, in the corresponding Qty text box, type 4
40. Click Order Completed Date/Time, then click its calendar and select the date for June 17, 2016
41. Press the Space bar and type 13:58
42. Click the Recommendations tab and, in the text box, type The whole tune was completed.
43. Close Microsoft Access

Previous	Copyright © 2002-2021, FunctionX	Next