First a few words on dutch names.

In dutch a surname can consist of two parts: a (optional) prfix plus a
surname. So "Piet van der Graaf" is a typical dutch name. In databases
dutch surnames are stored in two fields.

In my databes I have 4 fields which I want to be a ubique index:

- First name (Piet)

- Prefix to surname (van der)

- Surname (Graaf)

- Date of birth

I created a index (unique, not primary, ignore nulls). If all fields are
not null, everything works as hoped. Levaing the prefix null gives
unwanted results. If I enter 2 records (Jan,null , Punt, 01/01/1960)
Access aacepts both.

What can I do to create a unique index with an optional field?

TIA.

--
Groeten,

Peter
http://access.xps350.com


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

XPS35 <xps350 (AT) gmail (DOT) com> wrote in
news:i4ukea$1ibd$1 (AT) adenine (DOT) netfront.net:

I would set the prefix field to
1) default value = "" (the empty string).
2) Required = yes
3) allow Zero length = yes

The reason it's not working is that Null is never equal to anything, not
even another Null value.

However, realistically even with the addition of birthdate in there, there's
no guarantee it's going to be unique, so why even try?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)



"XPS35" <xps350 (AT) gmail (DOT) com> wrote

On 23 aug, 23:03, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:
Thanks for your reply. I wil try this option.

On 23 aug, 23:24, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
Douglas,

Thanks for your reply. I now understand why it did not work.

Your are right about the fields not being unique, but that wasn't my
issue.

On Aug 23, 11:06*pm, XPS350 <xps... (AT) gmail (DOT) com> wrote:
So, anyone, how can you create a primary key (or simulate it without
too much work) where one of the fields may be null or empty or have no
value? I have the same issue, a database table where the most natural
and useful key has a field that might be null.

Gilgamesh <michaelsflynn (AT) gmail (DOT) com> wrote in
news:e828008e-2b91-431b-b26a-fb61c67919b3 (AT) o7g2000prg (DOT) googlegroups.com
:

You can't. It violates the definition of a primary key to allow Null
in any fields. Null is not a value -- it means Unknown Value, and no
two Unknown Values can be known to be identical.

The only option, as already said, is to define a default value. One
way to do that is to allow ZLS and make "" the default value (and
set a validation rule of Not Null), but that makes querying a pain.
But it would get the job done.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"Gilgamesh" <michaelsflynn (AT) gmail (DOT) com> wrote

On Aug 23, 11:06 pm, XPS350 <xps... (AT) gmail (DOT) com> wrote:
A composit key that is NOT the primary key can have NULL values in the
fields. But because NULL <> NULL, all the other fields in the index can be
equal without failing the index.Which is why NULL is not allowed in a
primary key.

If you allow ZLS in the optional field and set the default value to "", the
empty fields will look the same as NULL. You will need to help your users in
the forms so if they delete the information in the field, it's replaced the
NULL with ZLS before saving the record

On Aug 23, 4:07*pm, XPS35 <xps... (AT) gmail (DOT) com> wrote:
Many times I have a field in my table generally called 'UKey' and when
I'm saving a record I create a unique key with this instead of trying
to create a primary key using other table fields

MyRec!UKey = Format(Date, "mmddyyyy") & Format(Time(), "hhnnss")

bobh.

"bobh" <vulcaned (AT) yahoo (DOT) com> wrote

On Aug 23, 4:07 pm, XPS35 <xps... (AT) gmail (DOT) com> wrote:
Seconds will not guarantee a unique value. Why not use a autonumber?