Proper way to setup a Yes/No/Null field in table and form? (2 Viewers)

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

 

[Pat Hartman]

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.

 

[CJ_London]

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

 

[Pat Hartman]

The image shows the field defined as number with a default of 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).

 

[Pat Hartman]

It appears Access sets the existing row values for the field to 0

Right, I forgot about that little detail. Over the years the MS folks have vacillated between defaulting to 0 or null for numeric fields. They have currently settled on defaulting to 0. This helps novices who don't understand how to handle nulls but is logically wrong in most cases and actually wrong in some.
1. Long Integers are primarily used as foreign keys. It is WRONG to allow FKs to default to 0. They need to default to null. Access can't tell the purpose of a field so it has no way to determine this at design time. By the time you create RI, it is too late.
2. Even though logically integers may be 0, it is better to default them to null but make them required so that the user is forced to enter a value. This is important when it comes to math, averages in particular. If the integer is for a test score and the test records are entered as a batch before the test is taken and the teacher then enters the scores after the fact, you have a situation where a student didn't take the test and if the teacher didn't remove the 0 default, the student ends up getting yelled at by his mother because his class average dropped because the average of (80, null, 90) = 85 but (80, 0, 90) = 56!!!! The point is ZERO has meaning.

Here's a tool that will help clean up tables after you design them

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

Just changing the default does not change the value of existing data so you need to use this code before the table is populated. Otherwise, you need to create update queries to change 0 to null or null to 0 depending on what you want to do.

 

[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

 

[Pat Hartman]

I would also go with null, True, False using a numeric field.