Infected04 said:
I need to disallow duplicate records entered for a number field BUT allow
the number 0 to be duplicated
only 0
can this be done?
Infected04, Yes, there is a way, but it is not advisable, since there are
two (2) important draw-backs:
1) You will not be able to enforce data entry on the specified field at the
table-level.
2) There will be many considerations when handling updates and reading on
queries, forms, reports, exports, and any other operations involving the
field.
Since zero (0) is not an actual number, then proceed as follow:
Set the "Required" property of the number field to "No."
Set the "Default Value" property of the field to "Null."
Set the "Indexed" property of the number field to "Yes - No Duplicates."
This will allow your table to have many records, with non-zero numbers,
while preserving the rule of not allowing duplicates.
[Considerations for data entry:]
For a numbers (different than 0) update the field.
For the number zero (0) do not update, or update the field with Null
[Considerations for data update:]
For a new number (different than 0) update the field.
For the number zero (0) update the field with Null
[Considerations when dealing with the field on queries, textboxes, etc.:]
Convert the field to a number using a field calculation, like in :
Val(nz([Field1],""))
-Randy
