Index and Null values

[john]

Last week I posted about making a unique index on multiple fields to prevent
importing identical records twice. I still have trouble with the nulls in
the index. The only way that I can make it work is to make all the index'
fields required fields and set the allow nulls setting to false. But then
every field in the index should always have some kind of value, and I have
to put a default value like "0" in them or "empty". Is this indeed the only
way to make this index work or is it somehow possible to make a unique index
where the nulls are part of that index?
Thanks,
john

[Bob Quintal]

"john" <jo**@test.comw rote in
news:lb******** ************@ca sema.nl:

Last week I posted about making a unique index on multiple
fields to prevent importing identical records twice. I still
have trouble with the nulls in the index. The only way that I
can make it work is to make all the index' fields required
fields and set the allow nulls setting to false. But then
every field in the index should always have some kind of
value, and I have to put a default value like "0" in them or
"empty". Is this indeed the only way to make this index work
or is it somehow possible to make a unique index where the
nulls are part of that index? Thanks,
john

You can set a default value for the field in the table design view.
You can also allow zero length strings.Use the two of them
together.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[john]

"Bob Quintal" <rq******@sPAmp atico.caschreef in bericht >>

You can set a default value for the field in the table design view.
You can also allow zero length strings.Use the two of them
together.

Thanks. For the text fields I now have Allow Zeros set to True, Required to
Yes, and Default value to "". But this doesn't work for date fields. Does
that mean that I can't have an empty value for a date field and that I need
a default value for that like "01-01-1900"? Doesn't seems very pretty.
john

[person]

On Sat, 2 Sep 2006 07:59:06 +0200, "john" <jo**@test.comw rote:

>Last week I posted about making a unique index on multiple fields to prevent
importing identical records twice. I still have trouble with the nulls in
the index. The only way that I can make it work is to make all the index'
fields required fields and set the allow nulls setting to false. But then
every field in the index should always have some kind of value, and I have
to put a default value like "0" in them or "empty". Is this indeed the only
way to make this index work or is it somehow possible to make a unique index
where the nulls are part of that index?
Thanks,
john

Not sure I understand what you are trying to do but:

You can have a unique index (though not a primary one) which contains nulls,
just remember that each null which occurs is treated (at least in Access, not
all dbs do this) as a different value, ie the records

ab; null;
ab; null;

are regarded as being different (since (null=null) is false).

Empty is really a concept which applies to variants, it means it has not been
initialised. Though you can use Isempty() to see whether a non-variant has
been intitialised, this works because of type conversion. Null is the concept
which applies to table entries. If you assign Empty to a variable it gives 0
or "" etc as appropriate via the automatic type conversion.

HTH

[Larry Linson]

Help me understand... it appears from what you write that you believe a
"unique index" means "unique except multiple records can have a Null"? In my
view, "unique index" means the field has to have a different value in every
Record (to identify the Record).

Allowing Nulls in a unique index would defeat that, notwithstanding the
purist definition of "Null" -- isn't equal to anything else, even another
Null. Searching the index on Null would return multiple Records if Nulls
were allowed. I also believe that using a Null String (zero-length string)
would defeat the purpose of unique index.

Larry Linson
Microsoft Access MVP

"john" <jo**@test.comw rote in message
news:68******** ************@ca sema.nl...

"Bob Quintal" <rq******@sPAmp atico.caschreef in bericht >>

>You can set a default value for the field in the table design view.
You can also allow zero length strings.Use the two of them
together.

Thanks. For the text fields I now have Allow Zeros set to True, Required
to Yes, and Default value to "". But this doesn't work for date fields.
Does that mean that I can't have an empty value for a date field and that
I need a default value for that like "01-01-1900"? Doesn't seems very
pretty.
john

[Larry Linson]

I should make clear that a non-PK unique index _can_ be specified to allow
Nulls, but I still think it defeats the purpose.

Larry Linson
Microsoft Access MVP

[john]

Person, Larry,
From my original post:
"I've made an import macro to import bank transactions with queries. Those
transactions
don't have an ID_number when downloaded from internet. They get it when they
are added to the main transaction table in my database. When adding them I
would like to check if they haven't accidentally been added already, based
on account nr, date, amount of money, and payers info."

Sometimes one or two fields are blank so if the following record already
exists in my main transaction table:
account date amount payers info
1234564 01-01-2006 90 This field has no value
and the user would accidentally try to add it again, that should not be
possible.
In Paradox I was used to the fact that null is identical to null in another
record, so the unique index would refuse the double entry of the above
mentioned record.
thanks,
john
"person" <sn**@snappers. comschreef in bericht
news:6h******** *************** *********@4ax.c om...

On Sat, 2 Sep 2006 07:59:06 +0200, "john" <jo**@test.comw rote:

>>Last week I posted about making a unique index on multiple fields to
prevent
importing identical records twice. I still have trouble with the nulls in
the index. The only way that I can make it work is to make all the index'
fields required fields and set the allow nulls setting to false. But then
every field in the index should always have some kind of value, and I have
to put a default value like "0" in them or "empty". Is this indeed the
only
way to make this index work or is it somehow possible to make a unique
index
where the nulls are part of that index?
Thanks,
john

Not sure I understand what you are trying to do but:

You can have a unique index (though not a primary one) which contains
nulls,
just remember that each null which occurs is treated (at least in Access,
not
all dbs do this) as a different value, ie the records

ab; null;
ab; null;

are regarded as being different (since (null=null) is false).

Empty is really a concept which applies to variants, it means it has not
been
initialised. Though you can use Isempty() to see whether a non-variant has
been intitialised, this works because of type conversion. Null is the
concept
which applies to table entries. If you assign Empty to a variable it gives
0
or "" etc as appropriate via the automatic type conversion.

HTH