Solved

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

Posted on 2008-10-02
6
213 Views
Last Modified: 2013-11-05
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

0
Comment
Question by:Mike Eghtebas
  • 3
  • 3
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22626413
Mike,

I think you had a single-quote too many.

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

Patrick
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 22626694
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22626959
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 22627362
How about month,and year. I alos need second because a sender may send more than one email in 60 second.

thanks,

Mike
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22627381
Mike,

Try a format string like this:

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

Patrick
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 22628629
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Find out what you should include to make the best professional email signature for your organization.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question