Solved

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

Posted on 2008-10-02
6
210 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
Comment Utility
Mike,

I think you had a single-quote too many.

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

Patrick
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
Mike,

Try a format string like this:

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

Patrick
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now