Yes/No Field

[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.

 

[Jeff Boyce]

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

 

[Ron2006]

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

 

[Jamie Collins]

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.

--

 

[Jamie Collins]

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.

--