Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Yes/No Field

Discussion in 'Information Technology' started by DRMOB, Jul 28, 2006.

  1. DRMOB

    DRMOB
    Expand Collapse
    Guest

    I have a number of fields that are Yes/No data types and the Display Control
    is a Textbox. I would like the default value of this field to be blank until
    the user enters Yes or No. Is this possible with this type of control? Also
    I'd like to know how the user could type just "Y" for yes or "N" for No.

    Also, if the field is marked "Yes" I would like the user to be forced to
    enter a date in another field. How can this be done. Thanks in advance.
     
  2. Loading...


  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    You can set a default value as part of your table definition.

    We may not mean the same thing by "blank" ... do you mean "blank" as in
    Null/no value, or blank as in No?

    If your table has multiple Yes/No fields, your database may need further
    normalization if you want to get good use of Access' features/functions.
    Multiple ("repeating") fields are common ... in spreadsheets! But neither
    necessary nor desirable in a relational database.

    One way you could have the user type a "Y" or an "N" on your form for Yes or
    No would be to use a combo box with these choices.

    If you add code to the control's AfterUpdate event, you can make another
    field accessible (e.g., enabled). But you'll probably have to wait until
    the form itself is ready to update (i.e., BeforeUpdate) to verify IF the
    user entered something in the related date-required field.

    Regards

    Jeff Boyce
    Microsoft Office/Access MVP

    "DRMOB" <DRMOB@discussions.microsoft.com> wrote in message
    news:8C114BAC-7D09-45DE-B57B-64E5A8DBC523@microsoft.com...
    >I have a number of fields that are Yes/No data types and the Display
    >Control
    > is a Textbox. I would like the default value of this field to be blank
    > until
    > the user enters Yes or No. Is this possible with this type of control?
    > Also
    > I'd like to know how the user could type just "Y" for yes or "N" for No.
    >
    > Also, if the field is marked "Yes" I would like the user to be forced to
    > enter a date in another field. How can this be done. Thanks in advance.
     
  4. Ron2006

    Ron2006
    Expand Collapse
    Guest

    If you are trying to create the situation for these fields of "Yes" /
    "No" / (blank) meaning not entered, the I would not advise a yes no
    attribute. A yes/no field does NOT have a third posibility. Yes/No is
    the same as True/False. It is always one or the other and once you have
    touched it I do not believe you can get it back to the neverbeentouched
    state. A query for true will get all those that are true, A query for
    false will get ALL the rest of the records.

    Use a text field and for loading use a combo with Yes No as the
    choices. This type of field can be made to be empty by deleting the
    entry or putting null in it. A query for Yes will get all the yeses, A
    query for No will get all the Nos and a Query for null or "" will get
    all that are NOT Yes and not No.

    Ron
     
  5. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    DRMOB wrote:
    > I would like the default value of this field to be blank until
    > the user enters Yes or No.
    > I'd like to know how the user could type just "Y" for yes or "N" for No.
    >
    > Also, if the field is marked "Yes" I would like the user to be forced to
    > enter a date in another field.


    'Tis the nature of seemingly 'boolean' data. First you're told the gate
    can be either open or shut, only later they need to know whether it's
    locked, has ever been opened, etc :)

    Best to avoid the YESNO data type completely and use something which
    can easily handle existing three value logic and any other values which
    may come along later (of course, a YESNO column value can be null e.g.
    in an OUTER JOIN).

    I would recommend you persist 'blank' values, rather use a
    human-readable placeholder (e.g. '{{NA}}' for 'not applicable') which
    you can replace with a zero-length string (or whatever) in your front
    end application.

    In SQL DDL it might look like this:

    CREATE TABLE Test4 (
    key_col INTEGER NOT NULL UNIQUE,
    data_col VARCHAR(6) DEFAULT '{{NA}}' NOT NULL,
    CHECK (data_col IN ('{{NA}}', 'Y', 'N')),
    effective_date DATETIME,
    CHECK (
    (data_col = 'Y' AND effective_date IS NOT NULL)
    OR (data_col <> 'Y' AND effective_date IS NULL)
    )
    );

    Jamie.

    --
     

Share This Page