oContacts.Items.Restrict Misses Some Contacts

BCVolkert

New Member
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
I have an Excel spreadsheet containing information that I use to create Outlook contacts using VBA. My VBA creates all the contacts correctly. However, when I need to update the contacts because the data changes or I need to make other changes to the VBA, some contacts are recreated rather than being updated as intended. All the contacts were created and are being updated with the same code. The data is a large list of patients and there are a few cases where the FirstName and LastName are common; so, I use the .Restrict method to create a Filter based on LastName, FirstName, and Birthday. These fields are also used to create the FileAs property; however, the Filter does not use or depend on that property.

To apply the Filter and avoid duplicating contacts when I repeat the creation/update process on multiple contacts:
  • I Set oFilteredContacts = oContacts.Items.Restrict(sFilter) in which sFilter includes LastName, FirstName, and Birthday.
  • For most of the contacts, the oFilteredContacts.Count = 1 and my VBA correctly updates or replaces the information in the existing ContactItem. If oFilteredContacts.Count = 0, a new contact is created and is populated with the correct values from the spreadsheet. For the most part this works. If oFilteredContacts.Count > 1, the process issues an error Msgbox and (after "OK") proceeds to the next record to be processed.
  • However, for several of the contacts known to exist, the oFilteredContacts.Count = 0 (wrong) and a new contact is created (bad). For other contacts created with the same code, the process works as intended and previously existing contacts are simply updated (good).
  • When I rerun the creation/update process, on the new set of oContacts (including the original contact and unwanted twin, oFilteredContacts.Count = 0 again and an additional twin is created - now triplets (getting worse). Repeating this continues to produce oFilteredContacts.Count = 0 for the troublesome records and the contact is redundantly created each time an update is attempted.
  • The Birthday portion of the sFilter is based on CStr(dtBirthday).
    e.g. Debug.Print sFilter yields [FirstName]='John' and [LastName]='Doe' and [Birthday]='3/15/1967'
    but, I've also used dtBirthday without conditioning it with CStr and end up with the same duplications for the same contacts -- even those created from 1st dtBirthday & 2nd dtBirthday or 1st CStr(dtBirthday) & 2nd CStr(dtBirthday). Debug.Print produces the same results for each permutation I've tried -- and that includes all that I can think to try.
This problem exists whether I do one contact, several, or all of them and it always seems to occur on the same contacts. I've deleted all 6287 contacts and recreated the lot of them from scratch only to have this Rasputin issue immediately return for the first attempt to update. Interestingly, if I remove the Birthday from the filter, everything works as intended; however, I do end up with botched results for the cases where Birthday is the missing differentiator as is appropriate in that circumstance.

The Birthdays come from Excel data in which they are all date values (i.e. the value of the cell is 24546 when displayed in General format; but, it is displayed in Date format as 3/15/1967). The problem also occurs for contacts in which the Birthday is empty in Excel. In these cases, the Birthday stored in the Contact using VBA is the Public Const EmptyDateValue = #1/1/4501# and Outlook displays this as expected (i.e. "None").

I've looked at the data in detail and have not been able to notice any pattern associated with whether or not a Contact avoids being detected by my .Restrict(sFilter). I've even recreated the Excel data while trying to make sure the Birthdays are handled as date values (i.e. real Birthdays are not text = "3/15/1967" and non-existent Birthdays are "" and replaced with the EmptyDateValue constant when the filter and contact are created).

To be complete: I'm using 64-bit Dell laptop / Windows 10 Pro / 32-bit Office 365 installed -- all legitimate, all updated, VBA project cleaned using MZ-Tools, all VBA in Personal.xlsb. I've repeated the process after a cold boot. Rasputin lives on.

I'd appreciate a nudge in the right direction. I fully expect a "duh! why did I not see that earlier" moment; but, a week of looking for the problem has probably left be blind to the obvious.

FYI Slipstick and Sue Mosher's books have been amazing sources of information to me and a major enabler in the progress I've made so far. I do appreciate your efforts and those of the other forum contributors very much.

Bruce
 

BCVolkert

New Member
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
I have an Excel spreadsheet containing information that I use to create Outlook contacts using VBA. My VBA creates all the contacts correctly. However, when I need to update the contacts because the data changes or I need to make other changes to the VBA, some contacts are recreated rather than being updated as intended. All the contacts were created and are being updated with the same code. The data is a large list of patients and there are a few cases where the FirstName and LastName are common; so, I use the .Restrict method to create a Filter based on LastName, FirstName, and Birthday. These fields are also used to create the FileAs property; however, the Filter does not use or depend on that property.

To apply the Filter and avoid duplicating contacts when I repeat the creation/update process on multiple contacts:
  • I Set oFilteredContacts = oContacts.Items.Restrict(sFilter) in which sFilter includes LastName, FirstName, and Birthday.
  • For most of the contacts, the oFilteredContacts.Count = 1 and my VBA correctly updates or replaces the information in the existing ContactItem. If oFilteredContacts.Count = 0, a new contact is created and is populated with the correct values from the spreadsheet. For the most part this works. If oFilteredContacts.Count > 1, the process issues an error Msgbox and (after "OK") proceeds to the next record to be processed.
  • However, for several of the contacts known to exist, the oFilteredContacts.Count = 0 (wrong) and a new contact is created (bad). For other contacts created with the same code, the process works as intended and previously existing contacts are simply updated (good).
  • When I rerun the creation/update process, on the new set of oContacts (including the original contact and unwanted twin, oFilteredContacts.Count = 0 again and an additional twin is created - now triplets (getting worse). Repeating this continues to produce oFilteredContacts.Count = 0 for the troublesome records and the contact is redundantly created each time an update is attempted.
  • The Birthday portion of the sFilter is based on CStr(dtBirthday).
    e.g. Debug.Print sFilter yields [FirstName]='John' and [LastName]='Doe' and [Birthday]='3/15/1967'
    but, I've also used dtBirthday without conditioning it with CStr and end up with the same duplications for the same contacts -- even those created from 1st dtBirthday & 2nd dtBirthday or 1st CStr(dtBirthday) & 2nd CStr(dtBirthday). Debug.Print produces the same results for each permutation I've tried -- and that includes all that I can think to try.
This problem exists whether I do one contact, several, or all of them and it always seems to occur on the same contacts. I've deleted all 6287 contacts and recreated the lot of them from scratch only to have this Rasputin issue immediately return for the first attempt to update. Interestingly, if I remove the Birthday from the filter, everything works as intended; however, I do end up with botched results for the cases where Birthday is the missing differentiator as is appropriate in that circumstance.

The Birthdays come from Excel data in which they are all date values (i.e. the value of the cell is 24546 when displayed in General format; but, it is displayed in Date format as 3/15/1967). The problem also occurs for contacts in which the Birthday is empty in Excel. In these cases, the Birthday stored in the Contact using VBA is the Public Const EmptyDateValue = #1/1/4501# and Outlook displays this as expected (i.e. "None").

I've looked at the data in detail and have not been able to notice any pattern associated with whether or not a Contact avoids being detected by my .Restrict(sFilter). I've even recreated the Excel data while trying to make sure the Birthdays are handled as date values (i.e. real Birthdays are not text = "3/15/1967" and non-existent Birthdays are "" and replaced with the EmptyDateValue constant when the filter and contact are created).

To be complete: I'm using 64-bit Dell laptop / Windows 10 Pro / 32-bit Office 365 installed -- all legitimate, all updated, VBA project cleaned using MZ-Tools, all VBA in Personal.xlsb. I've repeated the process after a cold boot. Rasputin lives on.

I'd appreciate a nudge in the right direction. I fully expect a "duh! why did I not see that earlier" moment; but, a week of looking for the problem has probably left be blind to the obvious.

FYI Slipstick and Sue Mosher's books have been amazing sources of information to me and a major enabler in the progress I've made so far. I do appreciate your efforts and those of the other forum contributors very much.

Bruce
After a few days here and no solution, I posted a link to this question at stackoverflow. Dmitry Streblechenko answered with a suggestion to change my filter on the date to a range. It worked!!!

My sFilter is now [FirstName] = 'John' and [LastName] = 'Doe' and [Birthday] >= '3/15/1967' and [Birthday] <= '3/15/1967 11:45:36 PM'.

The sFilter passed to the .Restrict method now looks like this:
[FirstName] = 'John' and [LastName] = 'Doe' and [Birthday] >= dtBirthday and [Birthday] <= dtbirthday + 0.99999.

The sFilter is built with four calls to a function that accepts the sFilter, Property Name, Value, and Operator as arguments and returns a new sFilter with the filter text for the new property appended to the sFilter input parameter.
 

BCVolkert

New Member
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
Dmitry is the best. :) Yeah, filtering on dates can be tricky.. You don't need to go to midnight - it should work just adding .1 to the value.
Thanks for the clarification. My first reaction was that he was trying to sell OutlookSpy; but, a bit of research told me he was a Microsoft MVP - always a Gold Standard of credibility.

I seem to recall trying >= dtBirthday - 0.5 to <= dtBirthday + 0.5 without success; but, there may have been a concurrent error of some sort that I have not identified.

On further reflection, I ended up with >= dtBirthday to < dtBirthday + 1. My reasoning is that with medical records it is at least plausible that a Birthday in a dataset may actually be a birth time (think twins) and that our cultural treatment of Birthday is an event in a particular day. I don't know about the impact of the wider range on computational speed; but, I suspect it's pretty small.

I doubt that it would make a difference for my situation; but, I find it sometimes helps to code for the edges in life. That way, the unlikely case is handled in stride.

I've learned a lot from your site and Sue's books. I read the 2003 version cover-to-cover a few years ago and I'm ploughing through the 2007 version now. Your site is the richest source of Outlook information I've found. Thanks again Diane.
 
Similar threads
Thread starter Title Forum Replies Date
D Outlook 2016 Outlook not saving Sent Items Using Outlook 4
G Retention Policy for Outlook.com Deleted Items folder Using Outlook 0
M Outlook on 3 Computers Shows Different Total Items on Same Account Using Outlook 3
T Changing Sent Items location in Outlook 2019 Using Outlook 0
K Use VBA to find Sender and Recipient from Microsfot 365 Journaled Email Items Outlook VBA and Custom Forms 3
A Connect to server to views items Using Outlook 0
D Mail in Sent Items Gone Using Outlook 12
R How to get the Items object of the default mailbox of a specific account in a multiple account Outlook? Outlook VBA and Custom Forms 0
A Cannot copy this folder because it may contain private items Using Outlook 0
GregS Outlook 2016 Sent Mail absent from Sent Mail or Sent Items Folders Using Outlook 4
C How to use VBA to show only items x days old or more Outlook VBA and Custom Forms 1
T Increasing the number of items that appear on the Categories list Using Outlook 2
T How can you include Junk Email in Search Results like you can include Deleted Items? Using Outlook 3
King Mustard Sort search groups by amount of items? Using Outlook 1
J Sent Items Folder NOT Showing Correct From Email Address Using Outlook 0
C Pull Outlook shared calendars items from Excel Outlook VBA and Custom Forms 4
J Outlook Reply > From > Other Email Address... > Address Not Showing in Sent Items... From Email Outlook VBA and Custom Forms 0
GregS Outlook 2016 Sent Items vs Sent Messages Using Outlook 2
M Sent mail not showing in Sent Items folder; but they can be found with Search Using Outlook 3
A Order of Recent Items in Attach File List Using Outlook 5
W Outlook 2010 some sent items marked unread now (was Ok before) Using Outlook 0
D Unopened message in inbox deleted and not in deleted items Using Outlook 3
T "cannot find the calendar folder for this item" - calendar items stuck in outbox Using Outlook 0
R Exporting recovered Deleted Items outside the Inbox Using Outlook 1
Commodore Folders always closed in move/copy items dialog box Using Outlook 3
O Outlook 365 - Toolbar - Close all items - missing? Using Outlook 3
B Clear Offline Items (Mail Folder) via VBA Outlook VBA and Custom Forms 1
A Move email items based on a list of email addresses Outlook VBA and Custom Forms 40
R Retention periods and Deleted Items Using Outlook 7
V Items missing in Sent folder Using Outlook 10
P Deleted Items - Unable to Iterate All of Items Outlook VBA and Custom Forms 1
P How do I convert outlook “to-do” items to “tasks” Outlook VBA and Custom Forms 1
N Tracking Mail items being moved to folders Outlook VBA and Custom Forms 5
icacream Can't the Deleted Items Folder be self cleaning ? Using Outlook 2
N Selecting multiple calendar items Using Outlook 4
S Display PF contact folder items to select contact to link to appointment Outlook VBA and Custom Forms 1
M Sent Items Folder is Present but Not Displayed Using Outlook 2
B Saving items under a folder Using Outlook 3
M Outlook.com autocomplete list invaded by strange items Using Outlook.com accounts in Outlook 7
O How to display number of items per .pst file Using Outlook 7
S Show Total Number Of Items Not Displaying Using Outlook 9
M How many days are items in Recover Deleted at Outlook.com ? Using Outlook.com accounts in Outlook 4
A Cant See outbox items on a secondary Account Exchange Server Administration 3
S Send email via SMTP - use transport rules to add to senders inbox (then rule to move to sent items Exchange Server Administration 1
J How do I get the items in a collapsed group? Outlook VBA and Custom Forms 12
J Accessing calendar items for Resource Mailboxes Outlook VBA and Custom Forms 2
S How to extract mail items from multiple folders and shared mailboxes? Outlook VBA and Custom Forms 0
A VB to "reply all" email items stored in a folder of outlook with adding a new message Outlook VBA and Custom Forms 0
C Color Code New Calendar Items Using Outlook 2
S VBA Code to move mail items from search folder to inbox subfolder Outlook VBA and Custom Forms 4

Similar threads

Top