Check constraints

Not to worry, TC - I just checked, and can confirm that in C#, you'd be
right. It's all a question of context! :-)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
Gak! Falls on face in mud. :-)

TC


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Actually, in VBA, Null Null is neither True nor False. It is Null.
Try
the following in the Immediate window ...

? nullnull
Null

Roger's suggestion will, as far as I can see, work with a Jet database.
I
believe it would break if the database were ever moved to SQL Server,
because of the different way in which the two database engines handle
Null
values in unique (not primary) indexes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
(NULL != NULL) is False - not True.

HTH,
TC


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would
not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location
is
designated as the main location. It does NOT require that at
least
one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte
is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered
into
the
field. The unique index ensures that only one IS_MAIN per company
can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint
on
the
table, or i could use a trigger. However, as far as i know,
neither
of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.

It's just a bit galling to fall for the same "schoolboy howler" trap
that I have explained to other people in the past!

Cheers,
TC


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message ...
Not to worry, TC - I just checked, and can confirm that in C#, you'd be
right. It's all a question of context! :-)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
Gak! Falls on face in mud. :-)

TC


"Brendan Reynolds" brenreyn at indigo dot ie wrote in message
...
Actually, in VBA, Null Null is neither True nor False. It is Null.
Try
the following in the Immediate window ...

? nullnull
Null

Roger's suggestion will, as far as I can see, work with a Jet database.
I
believe it would break if the database were ever moved to SQL Server,
because of the different way in which the two database engines handle
Null
values in unique (not primary) indexes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


"TC" wrote in message
...
(NULL != NULL) is False - not True.

HTH,
TC


"Aaron Abdis" wrote in message
...
Ah... okay, i think i see. Then multiple additional locations would
not
break the index because NULL != NULL, correct?

"Roger Carlson" wrote in message
...
Well, here's one way. However, it only checks that one location
is
designated as the main location. It does NOT require that at
least
one
location MUST be the main.

Anyway, your IS_MAIN field must be a number field (integer or byte
is
best).
This will not work with a Yes/No

1) Set the Default Value of IS_MAIN to null
2) Set the Validation rule to 1
3) Create a Unique (compound) Index on COMPANYID and IS_MAIN

The validation rule ensures that only a 1 or a null can be entered
into
the
field. The unique index ensures that only one IS_MAIN per company
can
have
a 1.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Aaron Abdis" wrote in message
...
Hello,

I have a table called Company_Locations. There is a field named
Is_Main.
For
any given COMPANYID, i want that only one Location can have the
IS_MAIN
checked. How can i do this?

In ORACLE or SQL*Server, i would simply throw a check constraint
on
the
table, or i could use a trigger. However, as far as i know,
neither
of
these
exists in Access. So how would i be able to do this?

Thanks in advance,

- Aaron.