?
Solved

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

Posted on 2008-10-02
6
Medium Priority
?
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 93

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 93

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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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