No dublicates except for zero value

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

I have a table I need to edit the design. One of the
fields has a property of Long Integer. I want to make
this field have no dublicates except for a zero value.
Due to the complex nature of the db I must keep the field
a Number value.

Is this possible?
 
One of the
fields has a property of Long Integer. I want to make
this field have no dublicates except for a zero value.
Due to the complex nature of the db I must keep the field
a Number value.
Click to expand...

What exactly do you mean by "no duplicates except for a zero"? If you mean
that you can have any number of zeroes, but any other number can only
appear once, then I am afraid that there is no way you can force this in
Access.

The closest you could come would be to convert the zeroes to Nulls (this
may or may not be appropriate, depending on your application) and then make
a Unique Index with Ignore Nulls set to Yes.

If that is not a reasonable solution, and you really need to impose the
rule, then you'll have to move up to an industrial RDBMS like SQL Server
(or MSDE) that offers Triggers.

HTH

Tim F
 
Back
Top