non-required boolean fields won't accept NULL 1

[dementg]

I'm trying to implement some boolean (yes/no) fields that are not required. If I don't know the correct value for that field, I want to be able to leave it in a NULL state. For some reason, as soon as I enter data for a new record, the boolean field immediately changes to No, and refuses to accept a NULL value. Any attempt to set it to NULL causes a "The value you entered isn't valid for this field" error. This happens when I use a triple state checkbox on a form, but also happens if I edit directly using the table's datasheet view. I am having this problem with Access 2000, but I just tried to do the same thing on Access 97 and it didn't work there either. Here are the settings I'm using for the field:

Format: Yes/No
Caption: <blank>
Default Value: Null
Validation Rule: <blank>
Validation Text: validation failed <I haven't seen this message appear>
Required: No
Indexed: No
Lookup Properties->Display Control: Text Box

I've tried all 3 formats (Yes/No, True/False, On/Off)
I've tried a validation rule: True Or False Or Is Null

My method for trying to enter a null value is this:
The Lookup properties for the field cause it to be displayed as a text box. I enter datasheet view for the table, and type Null as the value of the boolean field. I immediately get the error message. I've had the same problem if I try to use a triple state checkbox in a form linked to this field.

This seems like the most basic thing in the world, I have no idea why it won't work. I've run out of ideas, does anybody know how to make Null booleans work? Thanks.

 

[JeremyNYC]

Yes does not equal null. No does not equal no. A boolean field, by definition, can have one of only two values. Yes/True/On or No/False/Off. Null does not fit into that field, and it can't.

You can use an integer field in your table and then on your form use a check box with the TripleState property set to Yes. Clicking on that control, you'll cycle through gray/null, check/-1, and blank/0.

Some people feel it is an oversight of the Jet database engine that makes it so you can't do this in a yes/no field. It makes a good bit of sense to me. This &quot;workaround&quot; (if you're of the first pursuasion) is certainly quite usable.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[dementg]

I always thought that any data type could be null, even a boolean. I'm almost sure other databases work that way... I feel like debating the issue, but it doesn't matter. Thanks for clearing that up.