My database needs to have Boolean fields. Since there aren't any, the practice is to use a numeric field, and populate it with 0 or 1 (or any number for that matter).
I Googled how to make a checkbox control on a layout to indicate the value of the "Boolean" field, and it seems the conventional wisdom is to have a value list with only 1 in it, and make the layout field a checkbox set, pointing to that value list. The "1" shows up next to the checkbox, but if you size the field properly, that is not visible.
I'm having some issues, as some of my records have 0 in the pertinent field, and some have nothing, just empty. I'm grouping my report by the value of that field (descending), so instead of having 2 sections ("true" and "false"), I'm getting 3 sections. They consist of records with 1 in that numeric field, then records with 0, then records where the field is empty.
I think this is caused by my lack of understanding of how to use booleans. The early records are empty in that field, whereas all the later records have 0 in that field. I added another text field on my layout, also pointed to that same boolean field, to see what was happening. If there is a 0 in the indicator field, checking the checkbox causes a "?" to show up. If the field is empty, checking the checkbox causes a "1" to show up, and unchecking causes the field to become empty.
I can't find any way for the field to contain either "0" or "1" depending on whether I check or uncheck the corresponding checkbox, which is what I would expect. I tried making the value list 1 and 0, or 0 and 1, or "1" and "0", or "0" and "1", all to no avail. The checkbox only seems to work if there is a single 1 (or "1") in the value list.
So my question is: Is there a way to fix this to operate how I *thought* was properly, or should I simply change all the "0" values to empty and remove the requirement that the field be not empty, and also the 0 to 1 value requirement?
Thanks for any help...