Yesterday I added a test for a triple state checkbox which should allow null, and True/False values to be set.

I was reminded as I had initially tried to use an Access Yes/No field type that this does not have triple state. It can never be null, so therefore will not work when bound to a triple state checkbox field. The value simply won’t change when you click on the box.

So, I changed the type to a Short Text field allowing nulls. This works with the checkbox, however, allows multiple potentially illegal characters to be in the field. As many discuss in other forums, a better solution is to use an integer field in the table that allows nulls and bind the Triple State check box to that field.

This will force any text strings like true/false/yes/no to be translated to their Access integer equivalents of 0-False and -1-True (that’s a negative 1)

I’d also like to note here that in SQL server, a binary field for True/False actually uses values of 0-False and 1-True (that is a positive 1). To make matters more confusing, if you use a DAO, Access built query against a SQL server, it will translate the 1 to a negative 1 and work transparently in the background with this potential compatibility issue.

However, if you are using a Passthrough query going directly against the SQL server, you will want to be aware that your byte field will have to be either null, 0, or 1 as it will not accept a -1 value in the field.

This can also bite you if you are trying to compare the field directly to a -1.

Regardless, I am not currently using a SQL server backend with this application, so I will create a small integer field so it can be null, 0, or -1.

I changed the field type in the Access table to a number and set it to be an Integer. Making it a byte field would have caused problems with the -1 value of true as the byte field would only take 0 and 1 (or null)

An integer actually takes -255 to 255 I believe, so more values than we need, but enough to allow the 0, -1, null values needed to support the triple state checkbox out of the box.

Now that I have done this I will try to run my tests again. And I still have a test that is not completing due to an error since I am setting one of the values to null and the code is not able to compare the null value without the IsNull function or the Is Null operator. So let’s look again at where this is happening:

Private Function InputMatchesResult(FieldName As String, dctInputs As Scripting.Dictionary, dctResults As Scripting.Dictionary) As Boolean
    Dim retVal As Boolean
    Dim OldMatches As Boolean, NewMatches As Boolean
    OldMatches = (dctResults(FieldName).OldValue = dctInputs(FieldName)(0))
    NewMatches = (dctResults(FieldName).NewValue = dctInputs(FieldName)(1))
    retVal = OldMatches And NewMatches
    InputMatchesResult = retVal
End Function

This is a helper function in my test module. I need to have different comparisons depending on whether the values are null.

Often what I do in these cases is wrap the comparisons in the Nz function which returns the first argument with a non-null value. So let’s try this:

Private Function InputMatchesResult(FieldName As String, dctInputs As Scripting.Dictionary, dctResults As Scripting.Dictionary) As Boolean
    Dim retVal As Boolean
    Dim OldMatches As Boolean, NewMatches As Boolean
    OldMatches = (Nz(dctResults(FieldName).OldValue, "ValueIsNull") = Nz(dctInputs(FieldName)(0), "ValueIsNull"))
    NewMatches = (Nz(dctResults(FieldName).NewValue, "ValueIsNull") = Nz(dctInputs(FieldName)(1), "ValueIsNull"))
    retVal = OldMatches And NewMatches
    InputMatchesResult = retVal
End Function

And that worked:

So you will notice I am translating a Null value to a string “ValueIsNull”. The reason I’m doing that is so I can generally avoid collisions where I really want an empty string to not equal an actual null value. It is unlikely in my testing I will be trying to actually compare a string of “ValueIsNull” with an actual null value.

So in the next article I will look to add another new input field type, perhaps a radio button set.