Tips for Filtering Items Using Date-time Comparison
In my recent posting about a time-reporting macro, I mentioned a workaround to have the macro include appointments that fall outside of the normal work hours set in the Outlook Options dialog box.
Alert: If you have appointments in your date range that start and end before the beginning of work hours you have specified in the Outlook Options dialog box, for the purpose of running Items.Restrict for that date range, you should temporarily change the Start time under Work time in the Outlook Options dialog box, so that all appointments end after that Start time value. This is a temporary work around and you can change the Start time back to the desired value after running the macro.
I discovered a better way that does not require meddling with the normal work hours. This turns out to be a best practice I would like to recommend as well.
The issue in the previous time-reporting macro has to do with the way I attempted to declare myStart and myEnd as Date variables:
Dim mystart, myEnd As Date
And also the way I set the myStart and myEnd variables”
I also hard-coded the dates for the reporting period. To specify different dates, you should do that in two places. One place is the following:
'Hard-code the reporting dates just for simplicity in testing.
myStart = DateValue("09/27/2010")
myEnd = DateValue("10/02/2010")
The other is here:
'Reformat myStart and myEnd to account for minutes.
myStart = #9/27/2010 12:01:00 AM#
myEnd = #10/2/2010 12:01:00 AM#
Best Practice
In VBA, if you need to use variables in a date-time comparison, such as the myStart and myEnd variables in the filter string strRestriction for the restrict operation in this case:
strRestriction = "[Start] <= '" & myEnd _
& "' AND [End] >= '" & myStart & "'"
Set oResItems = oItems.Restrict(strRestriction)
You must declare them as Variant.
Dim myStart
Dim myEnd
Then use the Format function to initialize their values:
myStart = Format("09/27/2010", "mm/dd/yyyy hh:mm AMPM")
myEnd = Format("10/02/2010", "mm/dd/yyyy hh:mm AMPM")
Do not use the DateValue function to set these variables.
And, in the time-reporting macro, you no longer have to set the myStart and myEnd variables again to account for minutes, since they already do.
Just to point out as a related note, you must not specify seconds in a date-time comparison string.
If you do not use any variables in a date-time comparison string, specifying the date-time value using a Format function directly also works, as shown in the following example:
criteria = "[LastModificationTime] < '" _
& Format$("6/12/2005 3:30PM","General Date") & "'"
Updated Time-Reporting Macro
I updated the time reporting macro and attached it to this blog post.
The following assumptions still hold as in the previous posting:
a) The default calendar folder contains the appointments that you want to report time on.
b) The maximum number of categories you’d want to track is 21. If you are tracking more than 21 categories, you can search for the declarations for strAllCategories, iDurationPerCategory, and the line:
If iTotalCount >= 20 Then
And increase the maximum number of supported categories.
c) The reported date range is 9/27/2010 to 10/2/2010 in the macro. Adjust the reporting date range in the following lines for the dates you want to report on:
'Hard-code the reporting dates just for simplicity in testing.
myStart = Format("09/27/2010", "mm/dd/yyyy hh:mm AMPM")
myEnd = Format("10/02/2010", "mm/dd/yyyy hh:mm AMPM")
Then, to run the macro, copy the macro from the attached text file to the Visual Basic Editor and click F5.