Empty date field in indexes

[KingBroil]

Hi,

To prevent duplicate records, I use multiple fields indexes, which worked fine until now. I learned that each "empty" fields are consider unique by Access, so not the best in an index to prevent duplicate records. I managed to have it worked using the default value property to give each "empty" records the same value.

Now my problem is that I have a date field which is optional, but I need to use it as a unique identifier in an index. I could again use a default value, but since it has to be a date, I find it confusing. I would rather have a default value of "No date specified" or "-" or even better, "". Unfortunately none of those works with date fields. Any suggestions?

KingBroil

 

[spikepl]

And now forget what you've learned, and leave the date field empty i.e. Null.

 

[KingBroil]

Ok, and what about my index? Access still see each "Null" fields as unique. I still cannot use index to prevent duplicate entries because of that.

Any advices?

KB

 

[spikepl]

Let me see if I get it:

You have a composite index. One of its components is a date. If the date is not filled in, then you want the remainder of the index to be unique. If date is filled in, then the entire composite index must be unique, but the previous remainder may be duplicated (as long as each is paired with a different date). Is that it?

Update:

Also, you want to allow more than one record with empty date field. Or not?

 

[gemma-the-husky]

no - he is right. if you declare a unique index, with multiple fields, then you can end up with "duplicate" indices because one of the fields is null.

Dates are a particular problem. I got around this, by having to use a "special" date value of zero to ensure I could pick up duplicate keys - but then had to test for zero as a special case within queries etc.

OP - A date is a number which is why you need a default of zero (say)

Spike - let's say you have a special offer price which has an expiry date - but on occasion you can have a different special offer with no expiry date. In the latter case you want to leave the expiry date blank - but if you do, you can inadvertently set up multiple such "specials". So instead use an expiry date of zero to indicate it doesn't expire (or some artificial date well in the future, if the actual circumstance allows it) - as then you get a valid unique key.

 

[spikepl]

@Dave ,

Ok I see.

For display of these fields, if you follow Dave's trickery, you can still use "No date specified" or "" - no-one forces you to display the actual field from the table- you can in the form show a calculated (in the query) field, that displays whetever info you want, based on the content of the date field.

 

[KingBroil]

Mmm,

That's what I thought, I will have to select a date as default value, perhaps somewhere in the dark ages to avoid mistakes. I like Spikepl's idea to display something else in forms and reports though, that'll work for me.

Thanks both of you.

KB

 

[gemma-the-husky]

KB

It's a real pain, and unexpected to find this.


A long while ago, i tried to use a key that sometimes had a date, and sometimes I left the date blank, and got exactly the problem you describe.


The trouble was, when I tried to set all the blank dates to zero, access couldn't do it, because of duplicate key problems.


one catch that makes the fix spike mentioned tricky is that if in a query you have

datefield: iif(mydate=0,"No date",mydate)

then datefield is now a calculated field, and is automatically non-updateable - which is a pain if you need to be able to change the date!