Hi All,
I have a need to test a field at entry time to check if it is Unique. I know
there is a standard Unique checkbox in Field Validation, but I want to
validate for uniqueness using the Calculate box because I want the
uniqueness tested only under certain conditions - not a blanket test.
The problem is that I can't find a Unique function for field testing. I can
use the Lookup function on a Self Joining field, but that does not provide
the same flexibility as the standard Unique test.
Is there a separate Unique function, or how can I simulate it.

Regards Ron H.

You may put a self relationship based on that field and set in its
definition a calculation SUM of it having to be always 1.
Remi-Noel


"Ron H." <ronald_h70 (AT) hotttmail (DOT) commm> a écrit :

Hi Remi,
I am not sure how this is going to test that a text field is unique
especially when you consider the 2 scenarios of uniqueness
1. The field to be entered does not have similar data in the same position
elsewhere within the table
2. The field contents exist once in the table and that same unique value is
overwritten into that same position
Could you explain how to use SUM to test for this
Best Regards Ron H.

"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> wrote

Hi,
I don't understand your item 1. A field contents may be different from
record to record. Then saying you want its contents to be 'unique' means
you want to have each record to hold a contents of that field that does
not exist in any of the other records. Period.
If you want to have it 'unique' when compared to another field contents,
that is another story - not treated here -, as both fields are on the
same record.
Item2 is obvious : if you want the field contents to be unique among the
records, you sure want this to be true at any stage when filling its
contents.
Now, you are right with your last sentence : one should not use SUM but
COUNT.
Actually there are 2 ways to insure the uniqueness of the contents of a
given field :
1 - when defining FieldA set Control / Unique, and Strict respect of the
conditions, and message like : "There are duplicates values of FiledA !!
Please correct.
2 - define FieldA as a regular text field. Create a self relationship on
FieldA. Create a field named '#_of_dupl' =
COUNT(selfrelationship::FieldA) and another field named Warning =
CASE(#_of_dupl <= 1; ""; "DUPLICATE")
Remi-Noel


"Ron H." <ronald_h70 (AT) hotttmail (DOT) commm> a écrit dans le message de news:
gmHKe.81130$oJ.12203 (AT) news-server (DOT) bigpond.net.au...