Date and Time Values
Fundamentals of Times
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).
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 Regional and Language Settings of Control Panel:
To support time values, Microsoft Access uses a data type named Date. Actually, if you are creating a field in a table, to support a time value, you use a data type category called Date/Time. You can specify the type in Datasheet View or in Design View.
To create a time field in the Datasheet View of a table, after specifying the column name or clicking a cell under it, in the Data Type & Formatting section of the Datasheet tab of the ribbon, click the arrow of the Data Type combo box and select Date/Time.
If you want, you can use the Field Templates window:
If you are creating the table in Design View, to configure a field that can hold time values, specify its Data Type as Date/Time:
After setting the data type to Date/Time, you can further customize how the time values would appear on the field. Because there are different ways to display a time values, the Format property is used to specify how this value should appear:
As you can see, the Format property provides two columns of information. The left column shows the names of time formats. The right column shows a preview of each option.
The Format property is used to show how the time values should appear in the control. First, the user must enter the value. To assist the user with how he or she should enter the time, you use the Input Mask. To use it, you can 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.
To create a time value, you 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:
This would represent 5:42 AM. Another example is:
This also represents a time in the morning. Remember that you can also include the seconds in your time value.
There are two main ways you start with a time value. You can provide it to an object or you can get it from your database. If you are providing it, you can use the rules and combinations we reviewed above. On the other hand, if a time value already exists in your database, you can retrieve and use it as you see fit. In most cases, the users know how to specify a time value. In some cases, when getting a time, it may not be in a correct or recognizable format. Normally, before involving a value in a time-based operation, you should first check and convert it to a recognizable format. To support this, you can called a function called CDate. 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.
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, you can call the Hour() function. Its syntax is:
Hour(Expression) As Integer
To get the minute value of a time, you can call the Minute() function. Its syntax is:
Minute(Expression) As Integer
To get the second value of a time, you can call the Second() function. Its syntax is:
Second(Expression) As Integer
Each of these functions takes an argument that can be an expression that should produce a time value. The argument can also be the name of a control that holds a time value. The function then examines the argument and produces a number that represents the hour, the minute, or the second respectively.
Microsoft Access provides various functions to perform date and time related operations. These functions allow you to add times, find the difference between time values, or add constant values to time values.
To give you the current time, Microsoft Access provides a function called Time.
One of the operations you can perform on a time consists of adding a value to it. To support this, you can use the DateAdd() function. To find a backward time, you can use the DateDiff() function.
The DateAdd() function is used to add an interval time value to a specific time. With it, you can add a number of seconds, minutes, or hours to another time. The syntax of the DateAdd() function is
DateAdd(Interval, Number, date) As Time
The Interval argument is required and it specifies the kind of value you want to add. This argument is passed as a string, thus enclosed between double quotes 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.
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.
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 we will not discuss in our lessons), 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 but the decade is not used in calculations; after 100 years have occurred, the measure is called a century but the century is not used in calculations; after 1000 years have occurred, the measure is called a millennium but the millennium is not used in calculations).
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 Regional and Language Settings of Control Panel:
As reviewed for the time, 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 apply the date type in the Datasheet View of a table:
If creating a table in Design View, to configure a field that can hold any type of string, specify its Data Type as Date/Time.
After a valid date value has been entered in a field, you can specify how the date would display, not necessarily how the date was entered. The Format combo box provides already created formats you can use:
Those are the most popular formats used for dates. If none of those satisfies you, you can use the following characters or symbols to create a desired format:
To use these letters, simply type the desired combination in the Format field. 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 data entry person can enter a valid date. Once the field looses focus, it displays the date based on the format. Here is an example:
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 characters to separate them. An example would be January 5, 2004, 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:
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.
As mentioned for the time, before involving a value or an expression in a date-based operation, you should first convert it. To support this operation, you can call the CDate() function.
We saw that a date was made of the days, the months, and the years components. On an existing date, you can get these values by calling their corresponding functions. To get the year part of a date, you can call the Year() function whose syntax is:
Year(Expression) As Integer
This function takes an argument that represents a date value. It then isolates the year that it returns as a number.
To get the month of a date, you can call the Month() function whose syntax is:
Month(Expression) As Integer
The function considers a date value as argument, gets its year, and returns it.
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 get the name of a week, you can use the WeekdayName() function whose 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. 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 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.
As opposed to the time, if you want to know the current date on a computer, you can call a function named Date. To get both the current date and the current time of the computer, you can call a function named Now.
Some of the operations performed on date values are also performed using either the DateAdd() or the DateDiff() functions.
The DateAdd() function is used to add an interval date value to a specified date. It is used to add a number of days, weeks, months, or years to another 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 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, pass it as a negative value.
The date argument is the date to which you want to add the number.
The DateDiff() function is used to find the difference between two date values. 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, 7. There are other variances to that argument.
If your calculation involves weeks or finding 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.
|Previous||Copyright © 2008-2012 FunctionX||Next|