?
Solved

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

Posted on 2008-10-02
6
Medium Priority
?
230 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 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

864 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