Type mismatch or the value "2114078347" in the condition is not valid.

Providing SentBy (like 'Smith, Ken') I am able to open Outlook emails from Access environment. I am having trouble when I include SendOn (like #02/14/2008 14:30:10#).

This works:
strQuery = "[SenderName] = '" & txtSentBy & "'"  '<-- opens the emails

This doesn't work:
strQuery = "[SenderName] = '" & txtSentBy & "'" ' AND [SentOn] = #" & txtSentOn & "#"

The error message is:
Run-time error '-2147352567 (80020009)':
Type mismatch or the value "2114078347" in the condition is not valid.

Using Debug.strQuery I have:
[SenderName] = 'Tang, Sue' AND [SentOn] = #7/9/2008 12:22:09 PM#

Question: How can I correct this problem. I need to identify the emails via 'SendBy' and 'SendOn' at second level.

Is this possible?

Thank you.

For reference:
 
Public Sub OpenEmail(lngEmailID As Long)
 
    Dim dteSentOn As Date
    Dim strSentBy As String
    Dim lngFolderID As Long
    Dim strFolder As String
    
    lngFolderID = DLookup("EmailFolder_ID", "tEmails", "EmailID=" & lngEmailID)
    strFolder = DLookup("FolderPath", "tOLK_Folders", "ID=" & lngFolderID)
    dteSentOn = DLookup("SentOn", "tEmails", "EmailID=" & lngEmailID)
    strSentBy = DLookup("SenderName", "tEmails", "EmailID=" & lngEmailID)
    Call SearchForItem(dteSentOn, strSentBy, strFolder)
 
End Sub
 
Public Sub SearchForItem(txtSentOn As Date, txtSentBy As String, strOlkFolder As String)
 
    Dim obj_CurrentOlkFolder As Outlook.MAPIFolder
    Dim olkApp As Object, _
        olkNS As Object, _
        OlkFolder As Object, _
        olkItem As Object, _
        strQuery As String
    Set olkApp = GetObject(, "Outlook.Application")
    Set olkNS = olkApp.Session
    Set OlkFolder = GetOutlookFolder(strOlkFolder)
    strQuery = "[SenderName] = '" & txtSentBy & "' AND [SentOn] = #" & txtSentOn & "#" '
    'Debug.Print strQuery
    Set olkItem = OlkFolder.Items.Find(strQuery)   '<--- ERORRS HERE *********
    If TypeName(olkItem) = "nothing" Then
        MsgBox "No match found"
    Else
        olkItem.Display
    End If
    Set OlkFolder = Nothing
    Set olkNS = Nothing
    Set olkApp = Nothing
End Sub

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Mike,

Try a format string like this:

"m/d/yyyy h:nn:ss AM/PM"

Patrick
0
 
Patrick MatthewsCommented:
Mike,

I think you had a single-quote too many.

strQuery = "[SenderName] = '" & txtSentBy & "' AND [SentOn] = #" & txtSentOn & "#"

Patrick
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Patrick,

strQuery = "[SenderName] = '" & txtSentBy & "' AND [SentOn] = #" & txtSentOn & "#"

and

strQuery = "[SenderName] = '" & txtSentBy & "' AND [SentOn] = #" & txtSentOn & "#" '

are essentialy the same. ' at the gets ignored. I removed it. Still the same problem.

Mike
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Patrick MatthewsCommented:
Mike,

Here is the problem, quoting from the Outlook VBA help file:

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") & "'"


Regards,

Patrick
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
How about month,and year. I alos need second because a sender may send more than one email in 60 second.

thanks,

Mike
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I started tracking EntryID of the emails and used it in place od SentBy and SentOn.

Thanks,

Mike


Public Sub SearchForItem(strEntryID As String, strOlkFolder As String)
                                                   'not used--------------^
    Dim obj_CurrentOlkFolder As Outlook.MAPIFolder
    Dim olkApp As Object, _
        olkNS As Object, _
        OlkFolder As Object, _
        olkItem As Object, _
        strQuery As String
    Set olkApp = GetObject(, "Outlook.Application")
    Set olkNS = olkApp.Session
 
    'olkNS.GetItemFromID (strEntryID) '<------******
    Set olkItem = olkNS.GetItemFromID(strEntryID)
    
    If TypeName(olkItem) = "nothing" Then
            MsgBox "No match found"
    Else
            olkItem.Display
    End If
    
    Set OlkFolder = Nothing
    Set olkNS = Nothing
    Set olkApp = Nothing
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.