Proper way to setup a Yes/No/Null field in table and form?

[burma]

I'm creating a new Yes/No/Null field in a table. Null means unchosen. When the user leaves the form that has the field, they have to make a choice of Yes or No. What's the proper way to set this up? I don't think I want a rule in the table as I want to handle that in the form.

Here's my table design so far, not sure what I need in the form. The table will later go to SQL so I'm using integer datatype as I've seen recommended. Thanks

In Datasheet view, the values all seem to default to zero:

 

[CJ_London]

Just so you are aware, in access a null yes/no field will default to 0 when saved - it cannot be null

 

[burma]

You say you want to allow Yes/No/Null and yet the user has to choose between Yes and No before leaving the form. That is a conflict. Please clarify.

As I said, they have to make choice. The default should be Null (they haven't made a choice yet).

 

[burma]

The image shows the field defined as number with a default of null.

Please explain: 0 would be No, no?

 

[burma]

Further info: I created the field in an existing table. It appears Access sets the existing row values for the field to 0, even though I specified a default of Null. So I have to go and set all the values to Null to get that default?

 

[plog]

As I said, they have to make choice.

Then no need for NULL. Yes/No will work in the above instance, but that's not what you went on to describe. And the field isn't required in the table you have, so they don't realy have to make a choice.

I'd just have use the values 0, 1 & 2. 0=not chosen, 1=yes, 2=no. Make them all numbers and you don't have to do this sperate test for a different value type (NULL).

 

[ebs17]

Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases

Making decision despite missing SQL data (NULL): How to avoid OR…IS NULL in the WHERE clause.

modern-sql.com

A normal Boolean field has more than two states.

 

[burma]

This is what I'm leaning towards:

Although I like:

I'd just have use the values 0, 1 & 2. 0=not chosen, 1=yes, 2=no. Make them all numbers and you don't have to do this sperate test for a different value type (NULL).

Thanks all

 

[MajP]

If I was doing this I would use
Byte
default to Null
and stick with standard conventions
(-1 true, 0 false)

Now put a checkbox on the form and make it triple state. You then can cycle through yes, no, and null. You can even leave it null and the checkbox will show it as null

To ensure the user actively makes a selection

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim rtn As Long
  If IsNull(Me.yesNo) Then
     MsgBox "You have to actively pick true or false prior to saving.", vbInformation, "Make Active selection"
      Cancel = True
      Me.yesNo.SetFocus
  Else
     MsgBox "True or False: " & CBool(Me.yesNo)
  End If
End Sub

This makes logical sense because you can now check if it is true, false, or null.
cbool(-1) returns true
cbool(0) returns false.

 

[CJ_London]

Byte
default to Null
and stick with standard conventions
(-1 true, 0 false)

Bytes don’t have negative values

 

[MajP]

Sorry meant integer.

 

[LarryE]

Why can't the OP use a Yes/No Data Type?

 

[MajP]

Why can't the OP use a Yes/No Data Type?

The OP wants to ensure the user actively picks Yes or No. The way access works is even if the field is null to start and you do nothing as soon as the form is dirty the boolean field becomes false without actively being selected to false.

 

[LarryE]

The OP wants to ensure the user actively picks Yes or No. The way access works is even if the field is null to start and you do nothing as soon as the form is dirty the boolean field becomes false without actively being selected to false.

The Op might have some pretty angry users on their hands pretty quickly, if I read this procedure as it is intended. But OK I guess.

 

[CJ_London]

where I need a 'don't know' value, I use null, so when such a yes/no value needs to be stored, you could use a byte with values, null, 0 and 1.

?cbool(1)
True
?cbool(0)
False

However that means you cannot use a checkbox as ticking it generates a -1 value which is outside the range of the byte datatype (0..255), so generates an error. I tend to use my own checkbox design which is resizable and can have colour (e.g. green for true, red for false, black for null) so it works for me.

Otherwise use an integer with -1, null, 0 and you can still use a checkbox control