Is it possible to use a BIT field in a LibreOffice Base form?

[mrodent]

LO version: 7.2.6.2
OS: Windows 10
Dbase: MariaDB version “mysql Ver 15.1 Distrib 10.2.11-MariaDB, for Win64 (AMD64)”
Dbase connection: ODBC

I create a simple database and then a simple table. One of the fields is type “BIT”, length 1, NULL allowed.

I create a new ODBC data source.

I run Base and connect to this ODBC data source.

I go to “forms” and use the Wizard to create a new form (non-grid).

The form that then appears looks OK. But when I put something in any field and then try to save the data I get this:

Error inserting the new record
[MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.2.11-MariaDB]Data too long for column ‘bit_field’ at row 1

It doesn’t matter whether the BIT field is ticked or unticked.

… is there any known way actually to use a BIT field in a LO Base form?

If not, is there a way to “simulate” a one-BIT field, so that the user gets a tickbox (e.g. a TINYINT field, which could be set to 0 or 1)?

[mrodent]

A bit later… Yes, I see that the solution to this is apparently to use a “BOOLEAN” field, which then gets “translated” to a TINYINT field.

When you make a Base form the Wizard makes this become a text field, but you can change that into a checkbox. You have to fiddle with the size a bit, but it does mean you can achieve that.

Just one thing: in these circumstances it seems impossible to make this value NULL, using a form. Even if the underlying field allows NULL values, a checked checkbox corresponds to “1” and an unchecked one to “0”.

And… aha. It is also possible to achieve a tri-state checkbox with a TINYINT field, where, as a user of a Base form, you can set it to either 0, 1 or NULL: change the property “tri-state” to “Yes” … NULL is then represented by a sort of “minus”.

There is a slight niggle, however: even if the underlying field’s default value is NULL, appending a new record will not set your checkbox to NULL… it sets it by default to 0. Could be improved, but essentially a minor problem.