oContacts.Items.Restrict Misses Some Contacts

[BCVolkert]

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]

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.

 

[Diane Poremsky]

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.

 

[BCVolkert]

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.