Solved

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

Posted on 2008-10-02
6
217 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 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

724 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