Index and Null values

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

re: Index and Null values

"john" <john@test.comwrote in
news:lbWdndjJWMA4gGTZRVnyjQ@casema.nl:
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

re: Index and Null values

"Bob Quintal" <rquintal@sPAmpatico.caschreef in bericht >>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

re: Index and Null values

On Sat, 2 Sep 2006 07:59:06 +0200, "john" <john@test.comwrote:
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

re: Index and Null values

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" <john@test.comwrote in message
news:68WdnZz4RurfE2TZRVny2Q@casema.nl...

re: Index and Null values

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

re: Index and Null values

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" <snap@snappers.comschreef in bericht
news:6h9jf2dckg2970toituqbk9q350vkr6k15@4ax.com...