\[Editor's Note: The InstantDoc ID in the print version of Windows 2000 Magazine for downloading the code from Sue Mosher's "Taking Outlook to Task" March 2001 article was incorrect. To get to this article, click here. We apologize for any inconvenience this error might have caused.\]
In the January 2001 Outlook VBA on Demand column, I talked about purging a folder's contents according to a user-supplied date. But what happens when users enter dates in different formats? To answer that question, I want to review a few important procedures and techniques related to dates in Microsoft Outlook programming—particularly how to enter dates.
What is a date? In the context of Outlook, a date is any built-in or custom property that uses the Date/Time data type. In addition to working with such properties, Outlook programs often need to get dates from users through InputBox() functions or controls on a form. How can you tell whether the user has entered a valid date?
Fortunately, Microsoft Visual Basic (VB) provides an IsDate() function that lets you test for a valid date. IsDate() takes any date or string expression as its argument and returns True if the expression represents a valid date between January 1, 100, and December 31, 9999. In the January column, I used IsDate() to make sure that the user provides a valid date in an InputBox pop-up window:
strResponse = InputBox(strMsg, strTitle) If strResponse "" Or IsDate(strResponse) Then
IsDate() is smart about handling dates in a variety of formats. Any of the following expressions returns True:
- IsDate("31 Mar 2001")
You might wonder why IsDate("10:00") returns True. Remember that dates use the Date/Time data type, so a time component always exists, even if Outlook displays only the date.
If you've never worked with dates in VB or Microsoft Access, the expression IsDate(#3/31/01#) probably looks a little strange. The hash marks around the date are date quotes. Date quotes in date expressions are similar to regular quotation marks in string expressions—they inform the program unambiguously that the expression is a date, not a string or a calculation.
Compare the first and last expressions in the preceding list with IsDate(3/31/01). This expression, which contains a date surrounded by neither quotation marks nor hash marks, returns False. Because the argument isn't marked as a string or date, 3/31/01 acts as a numeric expression—3 divided by 31 divided by 1 (or about 0.0968). IsDate() correctly interprets the value as an unrecognizable date.
Another useful and versatile function is Format(), which converts various kinds of data into strings that use a specific format. In the January column, I used Format() to convert a user-entered date (strRestrict) into a string that displays the date in a specific format:
Format(strRestrict, "mmmm dd, yyyy")
For example, suppose a user enters the text 3/31/01 in the InputBox, setting the strRestrict variable. The above expression would return "March 31, 2001." The format is represented by "mmmm dd, yyyy", and you can build your own formats by combining elements that represent months, days, hours, minutes, and so on. For more information about the Format() function, see "User-Defined Date/Time Formats (Format Function)" in the VBA Help file.
For most purposes, though, you can use one of seven built-in named formats for dates, times, and date and time combined. The code you see in Listing 1 provides a convenient method to test the way any date or time appears. Run the code, then enter any date or time to see how the Format() function's named formats display it. I prefer the Medium Date format for many situations because it's both concise and unambiguous.
If you're building code that users in different countries might use, ambiguity is certainly a concern. People around the world use different formats for entering dates. Users set such preferences in the Control Panel Regional Options applet (which Figure 1 shows) or the Control Panel Settings applet, depending on which Windows version they use.
In particular, note the short date format. Most Americans would enter a March 7 date as 3/7/2001. But many Europeans, for example, would interpret that date as July 3, not March 7—they would enter the March 7 date as 7/3/2001. Format() understands these preferences and gets the date right every time. Figures 2 and 3 show the results of running the ShowDateFormats() subroutine on systems that have American English and French date preferences, respectively. In both cases, I entered 3/7/2001, but the French version shows the date as 3 juillet 2001 (i.e., July 3).
You've probably noticed that Outlook never leaves a date field blank. Instead, when a property doesn't have a specific date set, Outlook always displays the word None. The word None actually represents a specific date—1/1/4501. If you want to remove a date value from a date field and display None instead (e.g., you find that a particular task is open-ended and no longer has a due date), you can use one of the following statements, replacing the propname variable with the name of a specific built-in Outlook property, such as DueDate:
' for built-in properties Item.propname = #1/1/4501# ' for custom properties Item.UserProperties _ ("MyDateField") = #1/1/4501#
Now you know how to confirm that a user has entered a proper date, how to format dates in different ways, and how to set the special date that Outlook displays as None. These skills should help enhance any Outlook program you write that uses dates.