Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    I'm trying to prevent duplicates being created in a table when I import outlook messages.

    My logic is to compare the send and time the message was sent to determine if I already have the record.

    Code:
    strSQL = "SELECT * FROM tblOutlookMessage WHERE Message_From = """ & msg.SenderName & """ AND Message_Sent = #" & msg.SentOn & "#"
    Comparing the two dates fails to find the match and the resulting recordset is always empty.

    I've tried formatting the Date to US format before I use it in the Select statement

    Code:
    Format(msg.SentOn, "mm/dd/yyyy h:mm")
    Then only dates up until the 12th of each month get duplicated.

    Any tips or clues?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Is Message_Sent a Date or a Time or Date and Time ?

    Doing date comparisons in SQL requires the Date to be in mm/dd/yyyy format .

    So if it was just a Date I would be using

    Code:
    strSQL = "SELECT * FROM tblOutlookMessage WHERE Message_From = """ & msg.SenderName & """ AND Message_Sent = #" & format(msg.SentOn,"mm/dd/yyyy") & "#"
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks John, no it has to be data and time otherwise you might miss two messages from the same sender on the same day.

    I edited my original post while you were replying.

    As you can see I tried formatting in US format which does work if it's only the date but not if it's date and time.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In the format function m stands for months. You use it in one place to mean month, and the second time to mean minutes.

    Try
    Code:
    Format(msg.SentOn, "mm/dd/yyyy h:nn")
    Does the date stamp on the message include seconds as well?
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks John, sometimes I'm just not very bright, of course 'n' for minutes! I'll go again.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Much better, but I had to format the date when writing it into the field as well. Maybe due to differences in hundredths of seconds.

    Code:
             dteSent = Format(msg.SentOn, "mm/dd/yyyy h:nn:s")
             strSQL = "SELECT * FROM OutlookMessage WHERE Message_From = """ & strSender & """ AND Message_Sent = #" & dteSent & "#"
             Set rstMessage = dbs.OpenRecordset(strSQL)
             If rstMessage.EOF Then
                 rstMessage.AddNew
                     rstMessage!Message_From = strSender
                     rstMessage!Message_Subject = msg.Subject
                     rstMessage!Message_Body = msg.Body
                     rstMessage!Message_Sent = Format(msg.SentOn, "dd/mm/yyyy h:nn:s")

  7. #7
    New Lounger
    Join Date
    Jun 2010
    Location
    UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you convert your dates to longs you never have problems with date formats

    strSQL = "SELECT * FROM tblOutlookMessage WHERE Message_From = """ & msg.SenderName & """ AND Message_Sent = " & clng(msg.SentOn) & ""

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    That is an interesting suggestion.

    In this specific case, this would Not give the right answers however. Longs are whole numbers, and the time component of a date is stored in the decimal portion. So by converting to Long, you are discarding the time. As was pointed out earlier, the time component is important as there can be several messages on the one day.

    However, I imagine that converting to a Double instead would do the trick. (But I would want to test this to be sure it worked)
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Tested and confirmed - Converting it to a double before feeding it into the SQl string does do the job.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •