Faux Yes/No Fields

[kd2017]

Back when I first picked up access I absorbed much of Allen Browne's content and still refer back to it quite often to this day.

One habit I picked up early on was simply using integer fields instead of yes/no fields to completely avert the potential of building problematic queries that crash access (http://www.allenbrowne.com/bug-14.html).

Anyone around here still practice this?



I know Allen got out of the game a while ago so I can't help but wonder how much of his content needs some updating... The above referenced bug post is almost 17 years old now! I ran the example crash triggering query and there was no crash!

SELECT tblCompany.CompanyID, 
  tblEmployee.IsInvited, 
  Count(tblEmployee.EmployeeID)
    AS CountOfEmployeeID
FROM tblCompany
  LEFT JOIN tblEmployee 
    ON tblCompany.CompanyID
       = tblEmployee.CompanyID
GROUP BY tblCompany.CompanyID,
  tblEmployee.IsInvited;

(EDIT) I take that last part back, the above query DOES still cause the 'No current record' error. I mistakenly used inner instead of left join.

[isladogs]

Rather than Integer, I usually use Byte fields instead of Yes/No using values 0,1. Byte requires the least space of all the number fields and if you only need 2 values, Integer is overkill
It also solves the issue of updating boolean fields to True / False which fails for users with non-English language versions of Access

Most of Allen's site remains as relevant today as it was when written, although of course it doesn't cover any of the more recent features introduced since he retired.

[kd2017]

I found I couldn't use bytes along with check boxes via

CurrentDb.TableDefs("Table1").Fields("Field1").Properties("DisplayControl") = CInt(acCheckBox)

I assume because the check boxes are trying to set the field value to -1 which is out of range.

Is there a way around that?

[June7]

Maybe:

Abs(acCheckBox)

[isladogs]

I don't set the display control property for byte fields (where used in place of booleans) at table level using code.

In any case if its a byte field. the statement should be CByte(acCheckBox) not CInt.
Using CByte, it displays correctly as a checkbox as does June's suggestion of Abs(acCheckbox) BUT in both cases, attempting to edit the value causes an error

My approach has always been to do this in a form / query or report using a workaround with IIf statements to populate checkboxes based on byte fields.
All 6 checkboxes in this form are done in the same way and it works perfectly



However, if you aren't concerned about the extra storage space needed, use an Integer field and CInt(acCheckBox) as Allen Browne suggests.
That both displays correctly without any workaround needed and can be edited in a table, query or form.

Hope that helps.

[kd2017]

Abs(acCheckBox)

In any case if its a byte field. the statement should be CByte(acCheckBox) not CInt.

Using CByte, it displays correctly as a checkbox as does June's suggestion of Abs(acCheckbox) BUT in both cases, attempting to edit the value causes an error

In any case acCheckBox just evaluates to 106, and were setting the field's display control property constant, I would think this would be independent of the field's type?



I'm not sure I understand why any type conversion is necessary at all? Of course the code from post 3 also runs without any conversion at all...

The checkbox control has no problem displaying either way because anything not zero is true/checked. The checkbox just tries to set the field to -1 instead of 1 because that's what it expects for an access database I suppose. Of course we've all used checkboxs on our access front ends for different db engine backends that use 1 instead of -1 for true and they are fully functional... I wonder where the disconnect is? Perhaps the control is sending TRUE/FALSE to the engines and letting the engines convert to their perspective numeric values. (Side note: why in the world did m$ pick TRUE=-1 ??)

Space is plentiful, and cheap, in my cases so far. I haven't minded just using integers instead of bytes.

@isladogs, what is your approach/ui control for users setting bytes to true/false?

[CJ_London]

Although I have used checkboxes many times in the past, I'm moving more and more to using a 'colourful' textbox that looks like a checkbox but is resizeable and can be coloured using the format properties. You can also display alternative characters such as smiley faces (again using the format property).

Originally I used the wingding fonts for the characters but now use the high number characters (chrW) of a standard font

https://www.access-programmers.co.uk...eckbox.305659/

[isladogs]

I've no idea why using a Byte field and CInt(acCheckbox) fails but we both found that to be the case
It works fine with Integer datatype and that does allow the checkbox to be edited correctly

The Access team made 2 major errors with boolean fields - use of -1 for true and not allowing nulls. SQL Server & most other database engines don't follow the same mistake

One interesting side effect of this bad decision is that Access incorrectly sorts BIT fields. See Sort Bit Fields in Linked SQL Server Tables
As stated in my article, you can avoid the issue entirely by replacing SQL bit fields with SmallInt or Int datatypes using -1 for True as in Access.
Doing this removes all the complexity of True = [1|-1] for the sake of a little extra storage space.

After writing that article, I received a couple of very critical responses which stated that I clearly didn't understand the cumulative effect of unnecessarily adding to the storage space required in SQL Server.
The criticism was that this may be OK for a few hundred or thousand rows but not for large backends with several billion rows.

To which I responded that I wouldn't be using Access as an FE for such cases!

As for the UI, I only allow users to interact using forms.
The form in post #5 is deliberately read only but I have many similar editable forms with checkboxes setup in this way based on byte fields
Of course that means I then use code in the after update event to update the field in the background

Perhaps its time to finally change to Integer fields and use checkboxes directly. It certainly would be simpler!

[tobyd]

If you're going to use an Integer instead of a Byte or Yes/No, could you not then just go all the way and use a Long, per this thread?
https://www.utteraccess.com/topics/2066466

[pbaldy]

Post 9 was moderated, I'm posting to trigger email notifications.

[kd2017]

If you're going to use an Integer instead of a Byte or Yes/No, could you not then just go all the way and use a Long, per this thread?
https://www.utteraccess.com/topics/2066466

You can use it. I've always used integer as suggested by Allen Browne because it was the smallest size available that allowed a value of -1.

That's an interesting thread you brought up. I came across that same stackoverflow link in that thread recently myself a while ago and had the same thoughts about this. I suppose it's just a question of disk space efficiency and will that matter for your ms access project. A "byte" integer type is 1 bytes, an integer is 2, and a long is 4. I used to be more particular about picking the smallest field size for the job for storage efficiency's sake but I've gotten lazy with it recently. As I mentioned a few posts above storage is cheap. And I'm not working on large databases by any stretch. But as Colin mentioned above about working with billions of rows if that were my case I'd like to think I'd have the good sense to be more thoughtful about it...

I'll keep an eye on the utteraccess thread, thanks for linking to it.

[isladogs]

I'd already provided a link from the UA thread to here and had intended to do the same in reverse. Thanks to @tobyd for doing so.

I may be wrong but I doubt any possible speed benefits would make using Long instead of boolean a sensible choice.

Changing from boolean to byte needs no extra space, Using Integer needs one extra byte per record/field. Using Long needs 3 extra bytes

As a random example, assume I have 200 tables each with an average of 100,000 records and 5 boolean fields.

So using Boolean or Yes/No requires 200*100000*5*1 bytes = 100,000.000 bytes or 97,656 KB or 95.4 MB approx
Using Long Integer needs 4* more space so 381.5 MB approx.

That's almost 300 MB more for negligible benefit.
Hopefully the values are correct. The example may not be realistic but you should see the point.

The extra space needed could well be an issue in medium sized Access databases by making the file size so much larger that performance is adversely affected.