Dates in multi-field index

[Dave L]

I have an Access MDB table that contains three fields which I want to combine
and use as an index. The fields are a number and two dates. The second date
is optional. I want this index to allow only unique values for the data that
is supplied.

If all three fields are filled in, I get the behaviour that I want: I can't
enter another record with the same three values. However, if one or two of
the values are not entered (null), Access appears to ignore this record as an
index value.
ie: I can enter the same two values again, which I don't want.

Bottom line: any record that is missing one or two values is not included as
an index value.

I'm using Access 2003. The index is set up with Primary=no, Unique=Yes,
IgnoreNulls=no. Changing IgnoreNulls does not change the behaviour.

Any ideas on how to set up an index that will work with either two or three
values? Using two indexs (one for 2 values, one for 3 values) doesn't work,
since the 2 value case can duplicate part of the 3 value case.

I thought that IgnoreNulls would do this, but it doesn't...

Thanks,
Dave L

 

[Sharkbyte]

Have you tried setting your text field (in the table settings) to Required =
Yes and Allow Zero Length = No, and change your Date1 field to Required = Yes?

You have 2 different sets of requirements: table and index. Even though
the index doesn't enforce them properly, the table should.

HTH

Sharkbyte

 

[Dave L]

This misses the point. The data already gets into the table correctly. What
I need to do is prevent DUPLICATE data from getting into the table. If all
fields were always present, then the index would do it's thing and I would be
ok. Since 1 of the fields is optional, the index appears to quit working
when the optional data is not present and I have duplicate 2-value entries.

Dave L

 

[Sharkbyte]

My apologies. I tested what you are saying and could find no way to make
this work. You may be forced to validate the record through code.

Sharkbyte

 

[Tim Ferguson]

I thought that IgnoreNulls would do this, but it doesn't...

IgnoreNulls means that null values in the index column are simply not added
to the index. With a single-field index, this would indeed do you what you
would expect it to. Off-hand, I am not sure how it would work in a multi-
field index -- it sounds as though the entire record is omitted from the
index, rather than transmitting an error, which would be the only
alternative.

AFAIK, the only way to do what you want has to be a dummy "zero" date for
the missing value. This will allow a comparison to be made between values
in rows. Nulls cannot meaningfully be compared with other nulls or with
existing values, so the index engine would not know where to put the
entries with nulls.

Best wishes


Tim F