MS Outlook -.Restrict on ReceivedDate -> "Cannot Parse Condition.. 1

[kjv1611]

I put together some code in Outlook to get a count for any selected day (or range of days). I began with a loop that worked, eventually, but took too long on large folders.

Then I read about the Restrict method which creates a filer on the folder in code, and that sounds like a great idea. However, I'm having a hard time putting it to practice.

Depending upon how I calculate the date ranges, it does one of two things:
1. Starts the "day" at 8:00AM instead of 12:00AM if I do not specify a time.
2. Gives the error, "Cannot parse condition." if I specify the time, so that it covers the entire day.

Here's the code:

Private Sub cmdCount_Click()
    Dim intDayCount As Integer
    intDayCount = DateDiff("d", txtDateFrom, txtDateTo)
    If intDayCount > 4 Then '4 b/c The 2 dates will both be included, so 5 dates, diff gives 4 days.
        MsgBox "This procedure is only built to handle up to 5 days.  " & _
               "If you need more, talk to your current admin or analyst." _
               , vbCritical, "Too wide a date range"
        Exit Sub
    End If
    
    frmDateSelect.Hide
    Dim datDateRange() As Date [GREEN]'array to hold all dates[/GREEN]
    ReDim datDateRange(intDayCount) As Date
    Dim lngEmailCount() As Long [GREEN]'array to hold count of emails for each date[/GREEN]
    ReDim lngEmailCount(intDayCount) As Long
    Dim intDay As Integer [GREEN]'to loop through date array[/GREEN]
    
    Dim oFolder As Outlook.Folder
    Dim oItems As Outlook.Items
    Dim oItemsF As Outlook.Items
    Set oFolder = Application.ActiveExplorer.CurrentFolder
    Set oItems = oFolder.Items
    
    If DateValue(CDate(Me.txtDateFrom)) = DateValue(CDate(Me.txtDateTo)) Then
        datDateRange(0) = DateValue(CDate(Me.txtDateFrom))
    Else
        For intDay = 0 To DateDiff("d", txtDateFrom, txtDateTo)
            datDateRange(intDay) = DateValue(CDate(txtDateFrom) + intDay)
        Next intDay
    End If
    
    For intDay = LBound(datDateRange) To UBound(datDateRange)
        If intDay = UBound(datDateRange) Then
[b][GREEN]'This method works without error, but pulls in 8:00am for start of day instead of 12:00am[/GREEN]
			Set oItemsF = oItems.Restrict("[ReceivedTime] > '" & DateValue(datDateRange(intDay)) & "' " & _
                "And [ReceivedTime] <= '" & DateAdd("d", 1, DateValue(datDateRange(intDay))) & "'")
[GREEN]'****************
'This method gives correct dates, but doesn't parse correctly, so gives "Cannot parse" error[/GREEN]
            Set oItemsF = oItems.Restrict("[ReceivedTime] > '" & DateAdd("n", -1, datDateRange(intDay) & " 12:00 AM") & "'" & _
                " And [ReceivedTime] <= '" & DateAdd("d", 1, DateAdd("n", -1, datDateRange(intDay) & " 12:00 AM"))) & "'"
[GREEN]'****************[/GREEN]
        Else
[GREEN]'****************
'This method gives correct dates, but doesn't parse correctly, so gives "Cannot parse" error[/GREEN]
            Set oItemsF = oItems.Restrict("[ReceivedTime] > '" & DateAdd("n", -1, datDateRange(intDay) & " 12:00 AM") & "'" & _
                " And [ReceivedTime] <= '" & DateAdd("n", -1, datDateRange(intDay + 1) & " 12:00 AM")) & "'"
[GREEN]'****************[/GREEN][/b]
        End If
        lngEmailCount(intDay) = oItemsF.count
    Next intDay
   
    For intDay = 0 To UBound(datDateRange)
        frmDateSelect.Controls("txtDate" & intDay + 1) = Format$(datDateRange(intDay), "dddd, mmm dd, yyyy")
        frmDateSelect.Controls("txtEmailCount" & intDay + 1) = lngEmailCount(intDay)
    Next intDay
    
    frmDateSelect.Show

End Sub

Thanks in advance for any suggestions.

One thing I've thought about is (since the one method does work) is changing the default "start time" for the day to 12:00 from 8:00am, but I do not know what property to set, nor whether it can be set through code. So if that's an option, that may be the best option -... I can see doing that as this:

1. Capture Original Start of Day time in a variable
2. Set Default Start of Day to 12:00am
3. Run code
4. Set Default Start of Day back to original setting


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Gammachaser]

Outlook is a strange bird.

Your problem is not a default start time, it is the format you are using. I was baffled by this for a while myself and had built my own work around until I figured it out.

The proper format for the Items.Restrict when using a date such as CreationTime or ReceivedTime is summed up in the Items.Find section of the Outlook Object Model Reference:

Date

Although dates and times are typically stored with a Date format, the Find and Restrict methods require that the date and time be converted to a string representation. To make sure that the date is formatted as Microsoft Outlook expects, use the Format function. The following example creates a filter to find all contacts that have been modified after January 15, 1999 at 3:30 P.M.

sFilter = "[LastModificationTime] > '" & Format("1/15/99 3:30pm", "ddddd h:nn AMPM") & "'"

So, in order to get the proper results when doing an Items.Restrict, you have to use

Set oItemsF = oItems.Restrict("[ReceivedTime] > '" & Format(DateValue(datDateRange(intDay)), "ddddd h:nn AMPM") & "' " & _
                "And [ReceivedTime] <= '" & Format(DateAdd("d", 1, DateValue(datDateRange(intDay))),"ddddd h:nn AMPM") & "'")

I suspect you could also drop a lot of the 'cdate' and 'datevalue' fucntions if you are already starting with a string value in your controls txtDateFrom and txtDateTo because

Format(txtDateTo, "ddddd h:nn AMPM")

would work whether txtDateTo is a Date or a String.

 

[kjv1611]

Thanks a ton! I'll take a look and see if I can change it as you suggest. I did find a reference that sounds similar to what you're saying here, but just didn't have time yet to get back to this one:

http://blogs.msdn.com/b/officedevdo...ltering-items-using-date-time-comparison.aspx

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

It was the formatting issue, apparently. Thanks for the help on that. What you said was basically the same as what was in the blog post I found.

Here's the finished code that is working (and was also able to take out another piece or two that I was using before, just figured out it wasn't really necessary).

Option Explicit

Private Sub cmdCount_Click()
    Dim intDayCount As Integer
    Dim datTo As Variant 'for filter
    Dim datFrom As Variant 'for filter
    
    intDayCount = DateDiff("d", txtDateFrom, txtDateTo)
    If intDayCount > 4 Then '4 b/c The 2 dates will both be included, so 5 dates, diff gives 4 days.
        MsgBox "This procedure is only built to handle up to 5 days.  " & _
               "If you need more, talk to your current admin or analyst." _
               , vbCritical, "Too wide a date range"
        Exit Sub
    End If
    
    frmDateSelect.Hide
    Dim datDateRange() As Date 'array to hold all dates
    ReDim datDateRange(intDayCount) As Date
    Dim lngEmailCount() As Long 'array to hold count of emails for each date
    ReDim lngEmailCount(intDayCount) As Long
    Dim intDay As Integer 'to loop through date array
    
    Dim oFolder As Outlook.Folder
    Dim oItems As Outlook.Items
    Dim oItemsF As Outlook.Items
    Set oFolder = Application.ActiveExplorer.CurrentFolder
    Set oItems = oFolder.Items
    
    If DateValue(CDate(Me.txtDateFrom)) = DateValue(CDate(Me.txtDateTo)) Then
        datDateRange(0) = DateValue(CDate(Me.txtDateFrom))
    Else
        For intDay = 0 To DateDiff("d", txtDateFrom, txtDateTo)
            datDateRange(intDay) = DateValue(CDate(txtDateFrom) + intDay)
        Next intDay
    End If
    
    For intDay = LBound(datDateRange) To UBound(datDateRange)
        datFrom = Format$(DateAdd("n", -1, datDateRange(intDay) & " 12:00:00 AM"), "mm/dd/yyyy hh:mm AMPM")
        datTo = Format$(DateAdd("d", 1, DateAdd("n", -1, datDateRange(intDay) & " 12:00:00 AM")), "mm/dd/yyyy hh:mm AMPM")
        Set oItemsF = oItems.Restrict("[ReceivedTime] > '" & datFrom & "' And [ReceivedTime] <= '" & datTo & "'")
        lngEmailCount(intDay) = oItemsF.count
    Next intDay
   
    For intDay = 0 To UBound(datDateRange)
        frmDateSelect.Controls("txtDate" & intDay + 1) = Format$(datDateRange(intDay), "dddd, mmm dd, yyyy")
        frmDateSelect.Controls("txtEmailCount" & intDay + 1) = lngEmailCount(intDay)
    Next intDay
    
    frmDateSelect.Show

End Sub

Also, it's true that I could skip the date conversions and such in the code, but I'm actually using that to do some checking to make sure the user puts the dates in correctly.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57