Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    3 Fields combined No Dupliacates (2002 (XP))

    I have 3 fields (None are primary key) strFirstName, strLastName, and strPhone. I want to set a validation to ensure there will never be 2 records with all of theses fields the same (i.e. no two records with Smith, John, and 4135689909) I think I do this on the property sheet for the entire table but am not sure what the rule would look like. Any Help Appreciated

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: 3 Fields combined No Duplicates (2002 (XP))

    You can create a unique index for all three fields. If you try to save record where all 3 fields match existing record, you will get an error & will not be able to save record. Note, however, if you do not define this index as table's primary key, you WILL be able to save record where 1 or 2 of the fields are same, but other fields for both are Null. Example: If you define unique (not primary) index for First Name, Last Name, & Phone, you will not be able to save 2 records for MIKE JONES 1234567890. But you will be able to save 2 records for MIKE JONES where Phone is Null. If all 3 fields have Required property set to True, this should not be an issue. Also if the multiple field index is defined as primary key, Nulls will not be allowed in any of the fields which comprise the index.

    PS - To create multiple-field index, open Indexes dialog & select multiple fields as illustrated.

    HTH
    Attached Images Attached Images

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Massachusetts, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 3 Fields combined No Duplicates (2002 (XP))

    Thanks again Mark!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •