Re: Null field when checking for duplicates in an Index

Discussion in 'Access Table Design' started by John W. Vinson, Jan 18, 2011.

  1. On Tue, 18 Jan 2011 01:01:02 -0800 (PST), neil40 <>
    wrote:

    >Hi
    >
    >I found there is a way to check for duplicate entries to a table by
    >indexing fields, so I Indexed Forename, Surname and Nationality in
    >mine
    >
    >However, I have a need to sometimes have blank fields (EG no surname,
    >or forename) - a bit wierd but in this sport they sometimes
    >registered with a nickname - but the Index won't allow me to have a
    >Null field
    >
    >Is there any way around this?
    >
    >Thanks
    >Neil


    One way (and it's not ideal!) is to set the field's Allow Zero Length property
    to Yes, permitting a text string "" to be entered. If you do so, set its
    Default Value to "" and make it Required.

    A zero-length-string is still a string, and (unlike a NULL) it can be indexed
    and made part of a Primary Key.

    Do note that it would simply be *wrong* to force uniqueness on this
    combination of fields! I once worked with Dr. Lawrence David Wise and his
    colleague Dr. Lawrence David Wise, both US citizens. Names simply are not
    unique, and you can't base your database design on the assumption that they
    are.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Jan 18, 2011
    #1
    1. Advertisements

  2. On Tue, 18 Jan 2011 13:11:32 -0800 (PST), neil40 <>
    wrote:


    >Actually John, the more I think about it, the chances of having more
    >than one 'John Smith' are too great to ignore, so I think I will use
    >the check dupes query from time to time to see what shows up


    One good check is to use the data entry Form's BeforeUpdate event to check for
    duplicates. Either use DLookUp or open a Recordset to find existing names in a
    different record; if you find one, don't prohibit entry, but warn the user of
    the duplication and give them the choice to jump to the found record, add the
    new record anyway, or just cancel.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Jan 18, 2011
    #2
    1. Advertisements

  3. On Thu, 20 Jan 2011 01:47:40 -0800 (PST), neil40 <>
    wrote:

    >If anyone thinks DLookup is better than DCount, how would I change the
    >above?


    Well, the critical issue is whether there is another person with the same
    name; I don't think you really care if there are one, or three, or seventeen!
    DCount() makes the computer work harder than DLookUp(), because it must
    retrieve and count all the hits; DLookUp() can stop after it finds one.

    Try:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim iAns As Integer
    'Check for duplicate first and last name using DLookUp
    If Not IsNull( DLookUp("[Sur]", "[tblDrivers]", _
    "[Sur]= " & Chr(34) & Me![Sur] & Chr(34) & _
    " And [Fore] = " & Chr(34) & Me![Fore] & Chr(34)) Then
    Beep
    Ians = MsgBox("This Name already exists in the database. " _
    & "Please check that you are not entering a duplicate Driver " _
    & " before continuing. Click Yes to add anyway, No to quit.", _
    & vbYesNo, "Duplicate Value")
    If iAns = vbNo Then
    Cancel = True
    ' if the user clicks Yes, don't do anything, leave Cancel false
    End If
    End Sub

    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Jan 20, 2011
    #3
  4. On Sat, 22 Jan 2011 15:51:05 -0800 (PST), neil40 <>
    wrote:

    >However, (you contributed to a previous post on Form Design (and
    >ultimately Table Design)) my goal is to have a form where I will enter
    >Rally results and so it would have a drivers name and nationality (and
    >also a co-driver coincedentally), with car make/model, overall time
    >and points scored etc.
    >
    >As I stated in that post, I would like, for example, the Drivers name
    >to either fill by keystrokes (if he's already in the drivers table) or
    >be entered if he's a new competitor.
    >So..... would it be possible to do the checks as outlined in this
    >thread on various combinations of the field on the form. IE for this
    >case, the BeforeUpdate is in the Page properties


    Simply use a Combo Box to select from the existing set of drivers. Display the
    driver name and nationality, and any other information that would uniquely
    identify him or her. The combo will autocomplete, and it has a Not In List
    feature which will let you write code to pop up the data entry form for
    drivers.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Jan 23, 2011
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Özden Irmak
    Replies:
    2
    Views:
    251
    Özden Irmak
    Dec 14, 2003
  2. Susan K

    Multifield Index and Single field index

    Susan K, Feb 4, 2004, in forum: Access Table Design
    Replies:
    2
    Views:
    107
    John Vinson
    Feb 4, 2004
  3. Alp

    A field with no duplicates except for null

    Alp, Jan 2, 2005, in forum: Access Table Design
    Replies:
    6
    Views:
    235
    Allen Browne
    Jan 3, 2005
  4. theelio
    Replies:
    2
    Views:
    101
    Jerry Whittle
    Jun 27, 2006
  5. ZZBC
    Replies:
    6
    Views:
    145
    John W. Vinson
    Aug 22, 2007
  6. Flavelle Ballem

    Unique index not recognizing null

    Flavelle Ballem, Aug 20, 2008, in forum: Access Table Design
    Replies:
    17
    Views:
    206
    Flavelle Ballem
    Sep 26, 2008
  7. Desperate

    Index or primary key cannot contain a Null value

    Desperate, Jan 6, 2009, in forum: Access Table Design
    Replies:
    2
    Views:
    113
    John W. Vinson
    Jan 6, 2009
  8. Petr Danes

    To index or not to index

    Petr Danes, May 11, 2010, in forum: Access Table Design
    Replies:
    2
    Views:
    294
    Petr Danes
    May 12, 2010
Loading...