Filtering Items Using a Date-time Comparison

Article
06/14/2014

Outlook Developer Reference

Filtering Recurring Items in the Calendar Folder

To filter a collection of appointment items that include recurring appointments, you must use the Items collection. Use the Items.IncludeRecurrences property to specify that Items.Find or Items.Restrict should include recurring appointments. The Table object returns only one row representing the recurrent appointment item, instead of a row for each occurrence of the appointment.

Date-time Format of Comparison Strings

Date-time values are recognized according to the time format, short date format, and long date format settings in the Regional and Language Options applet in the Windows Control Panel.

Although dates and times are typically stored with a date format, filters using the Jet and DAV Searching and Locating (DASL) syntax require that the date-time value to be converted to a string representation. In Jet syntax, the date-time comparison string should be enclosed in either double quotes or single quotes. In DASL syntax, the date-time comparison string should be enclosed in single quotes.

To make sure that the date-time comparison string is formatted as Microsoft Office Outlook expects, use the Visual Basic for Applications Format function (or its equivalent in your programming language). The following example creates a Jet filter to find all contacts that have been modified before June 12, 2005 at 3:30 P.M local time.

  criteria = "[LastModificationTime] < '" _
         & Format$("6/12/2005 3:30PM","General Date") & "'"

Time Zones Used in Comparison

When an explicit built-in property is referenced in a Jet query with its explict string name, the comparison evaluates the property value and the date-time comparison string as local time values.

When a property is referenced in a DASL query by namespace, the comparison evaluates the property value and the date-time comparison string as Coordinated Universal Time (UTC) values. For example, the following DASL query finds all contacts that have been modified before June 12, 2005 at 3:30 pm, UTC.

  criteria = "@SQL=" & Chr(34) & "DAV:getlastmodified" & Chr(34) _
         & " < '" & Format$("6/12/2005 3:30PM","General Date") & "'"

Conversion to UTC for DASL Queries

Since DASL queries always perform date-time comparisons in UTC, if you use a date literal in a comparison string, you must use its UTC value for the comparison. You can use the Row.LocalTimeToUTC helper function or Outlook date-time macros to facilitate the conversion.

LocalTimeToUTC

One way to facilitate local time to UTC conversion is to use the helper function, LocalTimeToUTC, of the Row object. The following line of code uses this helper function to convert the value of the LastModificationTime property (which is a default column in all Table objects):

  Row.LocalTimeToUTC("LastModificationTime")

Outlook Date-time Macros

The date macros listed below return filter strings that compare the value of a given date-time property with a specified date in UTC; SchemaName is any valid date-time property referenced by namespace.

Note
Outlook date-time macros can be used only in DASL queries.

Macro	Syntax	Description
today	%today("SchemaName")%	Restricts for items with SchemaName property value equal to today
tomorrow	%tomorrow("SchemaName")%	Restricts for items with SchemaName property value equal to tomorrow
yesterday	%yesterday("SchemaName")%	Restricts for items with SchemaName property value equal to yesterday
next7days	%next7days("SchemaName")%	Restricts for items with SchemaName property value equal to next 7 days
last7days	%last7days("SchemaName")%	Restricts for items with SchemaName property value equal to last 7 days
nextweek	%nextweek("SchemaName")%	Restricts for items with SchemaName property value equal to next week
thisweek	%thisweek("SchemaName")%	Restricts for items with SchemaName property value equal to this week
lastweek	%lastweek("SchemaName")%	Restricts for items with SchemaName property value equal to last week
nextmonth	%nextmonth("SchemaName")%	Restricts for items with SchemaName property value equal to next month
thismonth	%thismonth("SchemaName")%	Restricts for items with SchemaName property value equal to this month
lastmonth	%lastmonth("SchemaName")%	Restricts for items with SchemaName property value equal to last month

Example Showing Conversion to UTC

The following code example illustrates three filter strings that return all messages received today, and applies one of the filters to Items.Restrict and Application.AdvancedSearch. It first uses PropertyAccessor.LocalTimeToUTC to convert today’s date to UTC date strings. The first filter uses the Outlook macro, today, to obtain a filter string that compares the ReceivedTime property with today's date in UTC. The second and third macros reference the ReceivedTime property by two different namespaces. The code example finally applies the third filter to items in the Inbox twice, first using Items.Restrict and then using Application.AdvancedSearch. It prints the number of items in the Inbox, and the number of items returned from each application of the filter.

  Public blnSearchComp As Boolean
Sub TestDASLDateComparison()
Dim strFilter As String
Dim colItems As Outlook.Items
Dim colRestrict As Outlook.Items
Dim oSearch As Outlook.Search
Dim oResults As Outlook.Results
Dim datStartUTC As Date
Dim datEndUTC As Date
Dim oMail As MailItem
Dim oPA As PropertyAccessor
Const SchemaPropTag As String = _
"http://schemas.microsoft.com/mapi/proptag/"
'Get items from Inbox
Set colItems = _
Application.Session.GetDefaultFolder(olFolderInbox).Items

'This code is a workaround to get today's date
'as UTC for DASL date comparison
Set oMail = Application.CreateItem(olMailItem)
Set oPA = oMail.PropertyAccessor
datStartUTC = oPA.LocalTimeToUTC(Date)
datEndUTC = oPA.LocalTimeToUTC(DateAdd("d", 1, Date))

'All three filters shown below will return the same results
'This filter uses DASL date macro for today
strFilter = "%today(" _
&amp; AddQuotes("urn:schemas:httpmail:datereceived") &amp; ")%"

'This filter uses urn:schemas:httpmail namespace
strFilter = AddQuotes("urn:schemas:httpmail:datereceived") _
&amp; " &gt; '" &amp; datStartUTC &amp; "' AND " _
&amp; AddQuotes("urn:schemas:httpmail:datereceived") _
&amp; " &lt; '" &amp; datEndUTC &amp; "'"

'This filter uses http://schemas.microsoft.com/mapi/proptag
strFilter = AddQuotes(SchemaPropTag &amp; "0x0E060040") _
&amp; " &gt; '" &amp; datStartUTC &amp; "' AND " _
&amp; AddQuotes(SchemaPropTag &amp; "0x0E060040") _
&amp; " &lt; '" &amp; datEndUTC &amp; "'"

'Count of items in Inbox
Debug.Print (colItems.Count)

'This call succeeds with @SQL prefix
Set colRestrict = colItems.Restrict("@SQL=" &amp; strFilter)
'Get count of restricted items
Debug.Print (colRestrict.Count)

Set oSearch = Application.AdvancedSearch("Inbox", strFilter, False)
While blnSearchComp = False
    DoEvents
Wend     

'Get count from Search object
Set oResults = oSearch.Results
Debug.Print (oResults.Count)

End Sub
Public Function AddQuotes(ByVal SchemaName As String) As String
On Error Resume Next
AddQuotes = Chr(34) & SchemaName & Chr(34)
End Function
Private Sub Application_AdvancedSearchComplete(ByVal SearchObject As Search)
MsgBox "The AdvancedSearchComplete Event fired"
blnSearchComp = True
End Sub