First Published 20 Oct 2024                                  Last Updated 27 Oct 2024                                   Difficulty Level: Easy


This article was prompted by an email from my Spanish Access developer colleague, Xevi Batlle.

Xevi had noticed that when sorting bit (boolean) fields in linked SQL Server tables, the results are the opposite of the expected values
For local / linked Access tables, boolean sorts give the expected results.

As an example, assume we have a list of students and want to sort these to show all students with no photo available first.
With an Access table, we can do this by selecting Sort Cleared to Selected


However doing this on a linked SQL Server table lists all students with a photo available first - the reverse of what is required.


To get the desired output on a linked SQL Server table, we need to Sort Selected to Cleared - the opposite of what we actually want!


The reason for this discrepancy is simple:
In Access, boolean values are 0 (false) and -1 (true). However, in SQL Server, boolean values are 0 (false) and 1 (true).

NOTE:
Confusingly, when not using checkboxes, Access DISPLAYS SQL Server bit True values as -1 (not 1).


Although the sort states Sort Selected to Cleared, what Access is doing is sorting Ascending in both cases.
Similarly selecting Sort Cleared to Selected, results in Access sorting Descending in both cases.

Behind the scenes, Access is in fact handling the SQL bit values using the actual values of 0 and 1

This is easily demonstrated by running a query on the SQL Server table and sorting ascending:




Similarly, running a query on the SQL Server table and sorting descending lists students with photos first

Exactly the same results are obtained by running a query on a SQL Server view.


Again you need to sort the bit field ascending to see the false values first when querying the linked view in Access.


NOTE:
As a further complication, SQL Server also allows null bit values which Access does not support. This is one important cause for Write Conflict Errors.

Although the cause of the sorting discrepancy is easy to understand, the results can be confusing to end users.

A simple solution might appear to be to use –[BooleanFieldName} in a query to give values of 0 and 1.
Whilst this works, trying to sort (or filter) by the boolean field gives an error:


Similarly, multiplying the boolean field by -1 also fails to solve the issue. The boolean field is sorted in ascending order so we should get 0 followed by 1 .


Although there are no errors, the sort still gives the 'wrong' results (with 1 followed by 0).




Workarounds

There are several workarounds that will successfully prevent this being an issue for end users:

1.   Use the Abs function

      The Abs function gives the absolute value of each number ignoring the +/- signs



2.   Use the Int function

      The Int function gives the integer value of a number. The results are as shown above for the Abs function.


3.   Use the Fix function

      Both Int and Fix remove the fractional part of a number and return the resulting integer value.

      The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to the number.
      However, Fix returns the first negative integer greater than or equal to the number.
      For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.

      In this context, the result is exactly the same as for using Abs.


4.   Use the CBool operator

      You can also use the CBool operator to force the SQL boolean field to sort 'correctly' in Access.
      Once again, the result is exactly the same as for using Abs.


5.   Use SmallInt datatype in SQL Server

      You can also 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.


Acknowledgements

      Thanks to the following Access developers including current and former MVPs for various suggestions that I included whilst writing this article:
      Xevi Batlle, Gustav Brock, Tom Wickerath and Peter Doering.


Further Reading

      A comment in Mike Wolfe's Week In Review blog article for 26 Oct 2024 reminded me about these two excellent related articles:
      •   Mike Wolfe           Yes/No Fields in SQL Server.
      •   Allen Browne       Why I stopped using Yes/No fields.


Feedback

      Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

      Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 27 Oct 2024

Return to Access Articles Page

Return to Top