The other day, I discovered a minor inconvenience in the way MS Access handles the “Yes/No” (or boolean) data type.

You should be aware, when working with MS Access, that any “Yes/No” field in your table can only accept boolean true and boolean false (or “true” and “false”, “yes” and “no”, 1 and 0, etc.). It will not accept NULL.

Standard programming practice is, when there is not a “default” value for a field, to insert NULL if the user does not explicitly declare a value. Unfortunately, the fact that Access throws an error if you try to insert NULL into a “Yes/No” field really puts a damper on that logic.

As the title says, this is just a quick warning for people that may be using MS Access for their databases. I’m not sure if the same is true for MS SQL or not, but it’s obviously a non-factor with MySQL, since MySQL doesn’t offer a boolean data type.

Databases offer a lot of flexibility when dealing with data. By spending some time upfront on the database design, it will save you plenty of headaches later on. Since the object of this to tutorial is not to elaborate on the advantages of using a database, I will skip to its limits.

The operations you can run on a database are always based on logic and numbers. So for example, when you run a store and sell T-Shirts, and you sort by size, “M” will always be sorted before “S”, even though it is not correct from a humanistic point of view.

Assuming we have a table called “Products” in the database, here is a set of data that you would typically deal with and a solution for sorting the data in a more useful way using the MySQL database.

Name    | Size

T-Shirt | L

T-Shirt | M

T-Shirt | S

T-Shirt | XL

T-Shirt | XS

And here is how you typically retrieve records:

SELECT Name, Size FROM Products ORDER BY Size

This query returns all products, however, not sorted “XS, S, M, L, XL” but in “L, M, S, XL, XS”.

The solution to this problem is a simple CASE (see: Control Flow Functions):

SELECT Name, Size,

CASE Size

WHEN XS THEN 1

WHEN S THEN 2

WHEN M THEN 3

WHEN L THEN 4

WHEN XL THEN 5

END

AS Sort_Order

FROM Products

ORDER BY Sort_Order ASC

This returns the products in order of their sizes. Use ASC (ascending) or DESC (descening) to change the order from smallest size to largest size or vice-versa.

That’s it, enjoy.