Yes/No and default values

[Bruce]

I have received valuable help from Cheryl Fischer and
others on this general subject. A few lingering questions
remain. Synopsis: I have a form with Yes and No check
boxes. In some cases checking No requires answering "Why
not?", and in some cases neither will be checked. I want
the check boxes to be white and blank until checked. In
the underlying table I used integer data type, no default
value. On the form I used an option group (tied to the
table field) with Yes -1, No 0, default 99 (I realize it
could have been anything other than -1 or 0, but 99
probably won't be confused with something else). It seems
I could have accomplished the same thing by making the
table field have a default value of 99. Does it matter
which I use?
On a related point, I got the same results as above when I
used text instead of integer as the data type. I had done
so in the hope of storing the values as Yes or No rather
than as -1 or 0, but could not get that to happen. Is
there a way of storing the Yes and No choices as text? It
will be easier to query, etc. in the future if I can do
so, and will not require remembering a number value.
Finally, there is a report based on the form. I want the
same check boxes as on the form, which I can do by either
creating an option group from scratch on the report, or by
copying one from the form to the report. Copying is
easier, or course, but is there a better way?

 

[Pavel Romashkin]

It is not very clear to me why are you trying to do it in any other way
than the built in, efficient and straightforward boolean, Yes/No Access
data type.
You are trying to use other data types to define the three built-in
values of a Yes/No type - "Yes" (-1), No (Zero) and Null (not defined).
This means that you can bypass all the code necessary to use Text or
Long and achive the same result using built-in features of Access.
As far as stroring Yes or No. You shouldn't try to *store* data in a
certain way. You should *format* the data in a way you'd want.
Particularly, a Yes/No field can be easily formatted to show Yes and No
depending on the value stored.

Good luck,
Pavel

 

[Chris]

Also, Boolean defaults to 0 being no, EVERYTHING ELSE
being Yes. So, 99 would still be shown as Yes.


Chris

 

[Bruce]

Boolean Yes/No allows for a Yes check box (or other
control to choose Yes), and the absence of a check mark
(or other control) is No. If there is just one check box
(for Yes) and the follow-up question to No (on the form)
is "Why not?", requiring an answer, then I was afraid it
would not be clear that the user is to leave the check box
blank and answer the follow-up question. I suppose I
could have done something like "If not Yes, explain", but
there is an additional problem: Not every question on the
form gets answered every time the form is used. I cannot
have a default check box. I need to have the option of
having both boxes blank. Yes/No, as I understand, does
not allow for a null value, or I would certainly have used
that. However, Null leaves a gray check box. It works
OK, I think, but is confusing to inexperienced users. I
am designing for them. This needs to be as intuitive as
is posssible for the users, even if it is less so for me.
I am trying to make their job easier, not mine at their
expense.
What if I were to ask the question another way: The
choices are Yes, No, and N/A? I realize I could get those
choices from a list box or combo box so that the data
would be stored as text, but if I want to use an option
group and check boxes, the data will be stored as a
number. That is a default with option groups, unless I am
missing something. Is there a way to store it as the
words "Yes", "No", or "N/A", or am I limited to the
numbers?>-----Original Message-----

 

[Pavel Romashkin]

I am sorry for not being clear enough.
A built in Yes/No type field can have 3 values - "Yes" (-1), No (Zero)
and Null (not defined). Make sure that Null values are Allowed in the
table design view to get the latter, and make sure Default value is NOT
set to No, or 0.
I mean, if you do not set a default value for the Yes/No field, it will
be Null. It is not the same as "clear", or Zero, or No. If you bind a
Yes/No field to a checkbox, that check box can be grayed out (Null),
white (No), or checked (Yes). However, using a check box there is no way
I know of to sett a Y/N to Null once it has been changed from Null to
either Y or N.
Use of a boolean field goes far beyond checkboxes, though. It is very
easy to use your option group with it, and nothing stops you from using
code to ask questions when users choose one option or another.
In my opinion, what is important is that data be stored properly. In
other words, I will not store 23/02/2004 as "Memo" just because it will
fit, and "Yes" as "Text" because it is human readable. Things like that
will make querying the database more difficult in the future.
Good luck,
Pavel

 

[Bruce]

Thank you for your replies. I have a situation where I
need to ask, for instance "Is statistical process control
used as a measure of capability?" If the user
checks "No", they have to answer (in words) "Why not?" It
could work to have just a single check box, I suppose, but
for several reasons it is better that "No" be an active
choice rather than a passive "Not Yes". This is not
entirely my decision, by the way. Also, it is likely that
N/A will be added to at least some questions, so I want to
have a structure in place to accomodate that. The
question becomes, then, how do I store N/A? As an
integer? Or what if the choices are Always, Sometimes,
and Never? Maybe I just need to use a combo box in such
cases, so that I can store the words. If the next
question offers the choices Agree, Neutral, and Disagree,
Always in one field and Agree in the next will be stored
as, say, the number 1. Queries and other data management
could become quite confusing. I would welcome suggestions
as to how I could best manage that situation. If a
limitation of check boxes, radio buttons, etc. in option
groups is that the user's choice must be stored as a
number, then I have to decide if it is the best choice.
Pardon me for going on at such length, but as my company
begins to convert to relational databases to store
information, I want to be sure to set it up as solidly as
possible from the beginning rather than going back in to
fix things later. Thanks again for taking the time to
offer your help and suggestions.

 

[Pavel Romashkin]

Please don't feel bad about asking! This is what this NG is for, and
those who feel annoyed are welcome to click somewhere else on the web.
Questionnaires are not my greatest strength. I still try to decide for
myself how to best design them to avoid hard coding of either forms or
data structures. So far it seems I have to do either one or the other.
If you have more than 2 choices, there is no way you can use a Yes/No
field, as well as if you may want to *set* a N/A value. I would use a
numeric field, and probably use small lookup tables like

tblAnswers
AnswerID (Long, 1 through 5, or more)
AnswerDesc (Text: No, Probably No, Not sure, Probably yes, Of course, etc.)

and then use option groups with radio button IDs of 1 to 5 and names
from AnswerDesc to return the proper ID to the questionnaire table. This
way it would be very easy to build a query to pull up all the
information by relating the questionnaire table to tblAnswers on the
AnswerID and grouping by whatever field you desire.
Definitely, for survey-type form a check box is not intuitive. I'd use
option groups throughout.
Since your application will have multiple choice questions, I think you
might as well stick with using an integer for Yes/No as well. In this
case, I think I would add these to tblAnswers as well, and use their IDs.
My dilemma is, I don't like to sit and create option groups, trying to
make sure that my radio buttons return the right IDs. I think I would
use a template table to record which answers will pertain to which
questions, and then write code to populate my form with right option
groups automatically.
Perhaps someone with more questionnaire experience will chime in with a
better idea.
Hope this helps,
Pavel

 

[Bruce]

Thanks again. I think I see what you are suggesting, and
I think I understand the principle. Since Access won't
accept an option value of "Text", I need to connect what
it will allow (a number) with the word I need. I wish I
could use combo boxes, but that is not one of the options
I have been given. Unfortunately there is nothing in the
Access Help index about lookup tables, only lookup fields,
which are different (I think).
It seems, from my searches of newsgroups, etc., that I
need to create a table as I would with a combo box, with
two fields (AnswerID and AnswerDescription) and relate the
option value (1, 2, 3, etc.) to the description, rather as
if I selected the number from a combo box but the bound
column is the text in another field in the same table.
If I am correct in this, then I expect I would need to add
code to make this happen. Would this be in the After
Update event of the check box (or radio button), the After
Update event of the option group, or some other choice?
Would I use DLookup to do that?
Thanks again for all of your help and suggestions.

 

[Pavel Romashkin]

Unfortunately, nobody with questionnaire experience answered, so I will
keep speculating.
The few questionnaires I came across were denormalized, with questions
as columns (fields). It was extremely difficult to query them by
customer, easier to query by response. The upside was, it is very easy
to build forms for these because you simply make each option group bound
to each question field. It was a royal pain to change them becasue you'd
have to add columns to tables and manually add option groups to forms.
If I were to design a questionnaire I think I would use unbound forms
and create those forms in code to avoid hard coding the answers.
As far as using combo boxes, I see no distinct advantage over option
groups other than it is a lot simpler to insert answers text in them in
another column. You don't need code for that, just a lot of typing -
setting up radio button values to AnswerIDs for those answers and their
names to the AnswerDescriptions. Using text for entries does not have
any advantages over using IDs related to the text because it is trivial
to obtain the text using a join query later.
My main problem in this case is the data structure design. Coding the
forms is not hard, once you have a good idea what you want them to do.
I think I may bite the bullet and write a sample questionnaire
application to see if I can make it self tuning and completely
normalized so I feel more confident offering advice in cases like this.
Then again, I port nearly everything to Palm to make data collection
convenient, so that poses additional design limitations.
Good luck,
Pavel

 

[Bruce]

Many thanks for all the time, thought, and effort that
have gone into your replies.