Boolean vs. Byte datatype

[bullfrog83]

In my one table I have a Yes/No field that I need to use in VBA. When I dim the variable which datatype should I use? Byte or boolean? I'd be inclined to use boolean but it's 2 bytes whereas byte is 1. And I've found that either one works. That is, I could have bytVariable = -1 or blnVariable = True. Is there a real difference? Does it matter?

[Jim Doherty]

@bullfrog83
Not really. Provided you are happy. 1 and 0 is as good true and false in my book. In fact ..... if and should you ever upscale to SQL server you will see that Access treats true and false values as null (if not instantiated to one or the other (true or false) it has a three logic state which breaks the big daddy database 'bit' datatypes which are bit data 1 and 0. Not insurmountable but a needless scalability consideration in my view

[NeoPa]

I recommend using Boolean fields and the True value. -1 (== True) is a 1 in every bit. It is not quite the same as non-zero. Boolean expressions return True and False (== 0), not 1 and 0.

The size consideration is not as important today as it was considered in earlier years. In fact, due to the importance of aligning variables on 32-bit boundaries anyway, Byte variables often (There are exceptions of course) take up 4 bytes anyway.

[bullfrog83]

@Jim Doherty
I do plan on moving my Access db to SQL server. So, if I understood you correctly, I should use byte instead of boolean?

[Jim Doherty]

@bullfrog83
Not necessarily.....I just mentioned that Yes/No fields in Access have a three logic state namely true false and null. Practically speaking it is the same as this....if you click the tick it is true if you click the tick and click it off again it is false. ...but if you never click it all and have NO default it will be null in the table row. Now what happens when you try and upload that to SQL Server as the natural equivalent which maps the 0 or 1 true/false BIT field in SQL Server it makes the server cough and reject your data upload. The nulls is a known problem on conversion. A quick search on the web will show you all sorts of discussions about this, It is not grave so dont let it concern your choice too much.

Don,t let this put you off, Its not insurmountable, its not major, it is just burdensome sometimes to go through a very sophisticated large db hunting out the upgrade problems thats all...If you want to use boolean then fine, go ahead and use it, just be aware about the providing the default in your table. If you do that then you will have no issue.

[bullfrog83]

@Jim Doherty
Ok, I understand what you were saying now. I always specify a default value for my Yes/No fields so I'm good. Thanks!