More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

Using the Find and Restrict methods to search for dates

All versions of Outlook support Find and Restrict methods for the Items collection that make it possible to retrieve members of that collection that meet certain criteria. Find returns a single item; you can get additional items by using FindNext. Restrict returns a new Items collection.

Working with dates in Find and Restrict is tricky, because many of the requirements are undocumented and unexpected. The page provides some basic information, as well as sample code that shows how to get all the appointments and events that a user might see in the Calendar for today.

Basics

Find and Restrict are methods of the MAPIFolder.Items collection that can be used to return a single item or a collection, respectively, that meet the search criteria.

Dates as strings not date literals

When using a date to search with the Find or Restrict method, the date in the search string must be formatted as an unambiguous string, not a date literal, and must contain only years, months, days, hours, and minutes -- no seconds. 

This is an example of a valid search string for all appointments begin on or after 24 Oct 2002:

[Start] <= "October 24, 2002 12:00 AM"

To make it easier to build such strings that contain internal quotation marks, I recommend using the following Quote function:

Assuming you already have an colItems collection from a calendar folder, the code to build and apply the above search string would look like this:

No seconds allowed

When working with a date from a variable or an Outlook property, you must apply formatting to the value to eliminate the seconds element that is intrinsic to any date/time variable or property.

The easiest way to do such formatting in VB or VBA is the Format() function, along with the "ddddd" date format. For example, this expression returns the current date/time in your locale's short date format plus the time without any seconds element:

Format(Now + 1, "ddddd hh:mm AMPM")

The Format() function is not available in VBScript, however. In many cases, you can use the  FormatDateTime() function, concatenating the date and time elements:

Alternatively, keeping in mind that users in different countries may reverse the order of day and month in short date formats, you may want to use a format that spells out the month, so that it is completely unambiguous. If you want an unambiguous date with the month spelled out, you can build it from the individual date/time components:

Recurring items

The Items collection for a calendar folder includes only the master instance of any recurring items. If you want to search for individual recurrences, you must add these statements before your Find or Restrict statement:

All Appointments for Specific Dates

Using some the basics above, we can now build one search string to locate the appointments for a specific day, both those that occur during the day and those that span the day.

Given a date/time variable named dteDate and an colItems collection from a calendar folder, these statements construct a search string that will locate all items on dteDate:

If dteDate is 24 Oct 2002 and my locale short date format is mm/dd/yyyy, the MsgBox statement will indicate that strFind has this value:

[Start]<= "10/24/2002 11:59 PM" AND [End] >  "10/24/2002 12:00 AM"

In other words, you look for all items that started before the end of the day in question and ended after the beginning of that day. It sounds weird, but try it and see! Many thanks to Slipstick visitor David Kennedy for the tip. For a graphic depiction of the six different cases that this approach covers, see Retrieving your Outlook appointments for a given date range.

This technique is flexible enough to cover a multi-day period of time. In the code sample All appointments for a date span, you'll find a DateSpan() function takes as its argument an Items collection (say all the items in a calendar folder), plus start and end dates, and returns a filtered Items collection of all items that take place during that span of time.

Also see:

Tips

Always build the search string using a separate variable, rather than trying to construct it inside the Restrict or Find statement. This will make debugging much easier.

Because search strings involving dates tend to be complex, it's a good idea to check the actual string after you build it. In VBScript code behind an Outlook form, include a MsgBox statement. In VBA or VB code, you can use Debug.Print to write to the Immediate window. For example:

You can comment out the MsgBox and Debug.Print lines after you confirm that the search string is working as designed.

Samples

  • Yearly Calendar view for Outlook - Outlook VBA macro to display appointments over a period of several months using an HTML file
  • Word Calendar templates - Three custom Word templates to print data from Outlook calendar folders. Good examples of how to loop through all items in an Outlook folder, build Word tables, color-code by category, etc.

More Information