Results 1 to 4 of 4

Thread: diary (2000)

  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    scotland, China
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    diary (2000)

    I want to create a diary using Access. Three of the fields in it will be SURNAME, PLACE and DATE. The following will be accepted SURNAME = smith , PLACE = Edinburgh and DATE = 30 March 2003
    SURNAME = smith , PLACE = Edinburgh and DATE = 29 March 2003
    but if a date clashes SURNAME = smith , PLACE = Edinburgh and DATE = 29 March 2003
    with the surname and place name I want it to flash up an error message.
    Any help please?
    If you have any suggestions of application packages that already do this I would also be grateful
    Thanks, in anticipation

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: diary (2000)

    Open your query in design view. Create a unique index on the three fields DATE, PLACE and SURNAME. It can be the primary key of the table, but that is not necessary.
    To create the index, make sure that the Indexes window is visible (View/Indexes). Enter a name for the index in the left column, for instance UniqueKey, then press Tab. Select DATE from the dropdown list in the second column. In the rows below, select PLACE and SURNAME, but *don't* enter a name in the left column. Return to the row with the index name, and set the Unique property to Yes. This will ensure that only unique combinations of the three fields are allowed.

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Location
    scotland, China
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: diary (2000)

    Many thanks Hans V for the answer - it works totally!!! - and I received it within an hour of posting!!!

    ALAS I did NOT make plain to you my full requirements of what could go wrong.

    If I WP SURNAME = smith , PLACE = Edinburgh and DATE = 30 March 2003
    and then I WP SURNAME = smith , PLACE = Edinburgh and DATE = 30 March 2003
    then Han's suggestion works admirably.

    But if I type SURNAME = smith , PLACE = Aberdeen and DATE = 30 March 2003
    the diary person to be in Aberdeen and Edinburgh on the same date, there is no error message. Any suggestions?
    Once again, Hans, many thanks.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: diary (2000)

    Your primary index should probably be Surname and Date.
    Pat

Posting Permissions

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