Fields definition type.

hi Ben,

a checkbox control on a form can be triple state ... but a Yes/No field in a table can only be True or False. I sometimes use Integer instead of Yes/No for data type so the field can be Null since Yes/No (Boolean) only has 2 choices -- Yes/True/On OR No/False/Off. Internally, Yes/No is actually stored as Byte, but it is crippled to not allow anything but 2 choices. Byte is not as transportable to other systems as Integer

If you want an Integer to appear as a checkbox (with triple state), you can set the DisplayControl to 106 in the table design for that field.

~crystal

free code you can use in your projects
https://msaccessgurus.com/code.htm

Null should not be used as N/A if that is your plan.  Null should mean "unknown"

Agree.

Now whats about the original question, what should the other fields be defined where there are no option of N/A (and definitely not other options).

In other words, is there any advantage of having all/most fields be defined alike?

Thanks,
Ben

hi Ben,

it is good to be consistent -- or use a naming convention so you can tell the difference. It doesn't really matter if some are integer and some are yes/no, but realize that Yes/No fields can create problems. Here is an interesting article by Allen Browne:

Why I stopped using Yes/No fields
http://allenbrowne.com/NoYesNo.html

~crystal

Hi Crystal,

I'm actually using Caspio which uses SQL as their back end, so those issues mentioned there probably does not apply here.
But you raised a good point, if I do go with different field types, at least let the name of the fields spell out clear their type (like MyFieldNameYN).

Thanks,
Ben

Just another comment:

When designing a table that will contain many Yes/No questions, some of them only have only options of Yes or No, while others have also N/A.

Depends on the concrete model. In a clean relational model I would expect one row per question, not a column per question. Thus an existing row can only have 1 or 0. And your "NULL" case means there is no row.

This can be taken further into:
We don't really need BIT/Boolean values as column data type in the relational model. E.g. a value like IsValid should be in the clean model an row in a table. No row means not valid.

Which leads me to this conclusion:
Yes/No, On/Off with or without unkown and not entered are in most cases states. So either use a lookup table, when you want to use numbers or use CHECK constraints on the columns to define the domain (allowed values as [N]VARCHAR).

Null would be a valid value to store in this context as it means, that the response for this question should be ignored when creating statistics for the questionaire.
You could use Byte and store Null, 0, or 1.

But the much simpler solution is to use Integer.
This can store either Null or the value directly from the checkbox "as is": 0 or -1.

But an int is 4 bytes.

You could use 2 bits per value: one for Y/N,  one for na/unknown.

Ben,

In other words, is there any advantage of having all/most fields be defined alike?

Sounds like you are ignoring our advice and creating a spreadsheet.  The survey model is well defined and this is not it.

Hi Experts,

It looks like I have no option of defining these fields as Yes/No since they will have to displayed as radio buttons, and in my case (Caspio as FE) I have only an option to display them that way if they are either integer/number or text fields.

However wondering what is the way to undo a selection of radio button, since one has the value of 0 and the other has 1, how do have this field set back to null?

Thanks,
Ben

Thanks to ALL Participants!