No dublicates except for zero value

[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?

 

[Tim Ferguson]

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.

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

 

[Guest]

Thank you for replying. I was afraid of that being the
answer.