Filtering on custom date field

[MED]

Hello,

I have customised a contact form, amongst other things, by adding several
fields with the type 'date'.

I have other fields that perform the function 'IsDate' on them to see if a
date has been entered into these fields. The custom date fields 'pass' this
test (both with and without a date having been entered).

However, when viewing the list of contacts in the folder where I have
applied the new form, I see a strange error when I apply a filter to the
'telephone list' view...

When I filter the list of contacts by adding a filter that tests one of my
custom date fields by doing 'exists' or 'does not exist', I see entries that
should fail the filter.

By that I mean that I have entries that do not have a date entered into the
filtered field, but when I filter on 'exists' on that date field, I see a
certain number of contacts that do not have a date in that field.

This is true for any custom date field in the 'buggy' contacts...

However, if I do the same filter on a built-in field, all contacts 'pass'
the filter test and I do not see any problems.

I have noticed that when doing any manipulation (via code) on a date field,
the first time the field is interrogated, I see that there seems to be noise
in the field. What I mean is that if I pop up a message box showing the
contents of the date field, I see entries like '01/01/4501' and
'01/01/1899' - even though the field is empty and looking at the contact the
word 'None' is displayed.

I see this error using Exchange 2000, Exchange 2003, using outlook 2002 and
2000. All SPs have been installed.

I am not sure if the problem comes from the fact that it is a custom date
field, or if there are other fields disrupting the filter, or if the
contacts themselves are somehow buggy. Some contacts are very old (they
were created using older versions of outlook) and some were added via a
vcard. However, of the contacts I have problems with, not all of them were
vcards, and not all of them originate from an older version of outlook...

If anybody has any ideas on why a date field that has a value of 'None'
(default value added by the system when no date has been entered into a date
filed) is picked up by a filter which filters on 'exists' I would be very
grateful!

Cheers,
Mike.

 

[Sue Mosher [MVP-Outlook]]

Do you remember what steps you followed to add those fields? It's certainly
possible to add a control to a form in such a way that the field is not
actually present in the item until data is added to that control. See
http://www.outlookcode.com/d/fields.htm for more information on this key
forms issue.

Outlook stores #1/1/4501# as a "null" date. A date/time field properly
created on the item never is actually empty, because it always has at least
that date.

I have occasionally seen the #1/1/1899# date, but not enough to have figured
out the exact circumstances.

You might see where this is leading: A better filter than exists might be a
filter on or before a date absurdly in the future. That should filter out
the "None" dates.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[MED]

Hi Sue,

The fields were added via the 'new..' button in the properties for the
object. All date fields will have content (regarding the existing contacts)
because I needed to 'update' the old contacts to use the new form.
I have added code to display the week number for the entered date into
another field. this is easily done, except the user needed to have the
choice of either entering a date (and the week number field was
automatically updated) OR the user could enter the week number, and the
corresponding date was automtically entered into the date field (the
corresponding Monday of the entered week). When I updated the old contacts
to use the new form, I found that the week number fields were not updated,
even though there was a date in the date field beause I use the contol
thingy to watch for the change of focus (programmed in the code). this
focus was not set-off when the field was updated by the loading of the form.
I needed to 'force' the focus code by running the subroutine within the
code.

However, doing this meant that I got other problems with strange dates being
entered if I tried to add a 'NULL' or equivelent. I cheated by just exiting
the subroutine if the date found was after the year 3000! This meant that
the original date was NULL (or 01/01/4501) and so no 'update' of the code
was needed.

In fact, I have implemented exactly what you suggest. instead of exist I do
an 'on or before 01/01/3000' and for does not exist I do two filters: 'on or
after 01/01/3000' AND 'does not exist', both on the same field. The reason
for two filters is that there are contacts where the 'does not exist' works
and others where it does not.

I can understand that the field needs some kind of entry (even if it is
NULL), but it is a shame that there isn't a special value that can be
interpreted as NULL consistantly!

Thanks anyway!

Cheers,
Mike.

 

[Sue Mosher [MVP-Outlook]]

What does "properties for the object" mean -- the form, the item, or the
folder? For best results, a custom field needs to be defined in both the
folder and the custom form. See http://www.outlookcode.com/d/fields.htm for
more information on this issue.

Outlook date/time fields don't support null values, hence the use of the
#1/1/4501# value for "None."

Once you have all the items using the same form, you might want to group the
items on your custom field. If you see two "None" fields, then use the
grouping method described at
http://www.slipstick.com/contacts/searchreplacecompany.htm to update all
"None" items to some ridiculous date, then back to "None." That should get
them all on the same track.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers