Re: Primary Index and Null values


"paii, Ron" <paii@xxxxxxxxxxxxxx> wrote in message
news:k_udna5vPuiRjr3Z4p2dnA@xxxxxxxxxxxxxx

"Anthony England" <aengland@xxxxxxxxxx> wrote in message
news:dvp2m1$p9v$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
"Cyberwolf" <jgaylord@xxxxxxxxxxxxxxxx> wrote in message
news:1142949390.803387.22920@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Is there a way to set a primary index to ignore Nulls and if so how
would I do it. I have already tried setting it so it is not a required
field, but that does not work. It states that the field can not
contain null values.

You cannot do this for your primary key.
If you want a field to have a unique index but ignore nulls, then you can
have this but it is not the primary key. Just set the properties:
Required=No
Indexed=Yes (No Duplicates)



(No Duplicates) could fail if more then one record had Null in the field.
Also any new records after the 1st Null would require a value for the
insert
to succeed.


I just created a table with two fields:
F1 = Long Integer, Not Required, Indexed No Duplicates, Not Required
F2 = Text, Required, Do Not Allow Zero Length, Not Indexed

I could enter the following records:

ID F1
1 ABC
Null DEF
Null GHI
2 ABC

However, any attempt to add another record with ID=1 or ID=2 is rejected.

Could you be mistaken in your assertion?


.

Relevant Pages

  • Re: Primary Index and Null values
    ... You cannot do this for your primary key. ... Indexed=Yes (No Duplicates) ... ABC ... fail on any new records. ...
    (comp.databases.ms-access)
  • Re: Primary Index and Null values
    ... You cannot do this for your primary key. ... Indexed=Yes (No Duplicates) ... ABC ... fail on any new records. ...
    (comp.databases.ms-access)
  • Re: Combo Box Design and/or Coding
    ... Primary Key: HotelID, Indexed, Yes (No Duplicates), Data Type: AutoNumber ... Primary Key: LocationID, Indexed, Yes, Data Type: AutoNumber ... nothing is being updated in tblHotelLocations. ...
    (microsoft.public.access.formscoding)
  • Re: Compositkey dilema...
    ... If I know that the second such occurrence will supersede the first one, or if there is some external reason that duplicates are never proper, there should be no problem. ... I agree that you may want to define a "No Duplicates" multi-field index on these two foreign key fields if they are not used as a primary key, but by doing so, you re-introduce the problem discussed in paragraph 1. ... couldn't you dispense entirely with [tblStudentClasses]? ... In, you could either let the 2 fields from plus the new [tblTestsID] field form the Primary Key, or just specify that be an Autonumber or otherwise unique without reference to any other fields. ...
    (microsoft.public.access.gettingstarted)
  • Importing data from different databases.
    ... If you change the primary key then for the best results ... it's possible that we could have duplicates ... >How can we resolve this problem? ...
    (microsoft.public.sqlserver.programming)