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.

 

[Steve Schapel]

Andy,

I think the only way you could handle this is via code on your data
form(s). Not sure of your specific requirements, but here is a sketch
example of the kind of thing... on the Before Update event of the
control bound to this number field...

If Me.YourField <> 0 Then
If DCount("*","YourTable","YourField=" & Me.YourField)=0 Then
' proceed
Else
MsgBox "Sorry, " & Me.YourField & " is already in use!"
Me.YourField.Undo
End If
End If

- Steve Schapel, Microsoft Access MVP

 

[John Vinson]

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?

Not as stated, no; however, if you can accept a NULL as the
"unassigned" value, Access will allow any number of records with NULLs
while still preventing duplicates in the non-NULL values. If you want
to treat these nulls as zero for purpose of calculations, just use the
NZ() function to convert Null to Zero.

If you decide to go this route, check that the field's Required
property is False, and that its Default property is blank (instead of
the zero all-too-helpfully provided by Microsoft for all numeric
fields).