Of all the data type differences between Access and server databases, Boolean fields cause the most subtle bugs. Not the most dramatic bugs — those belong to dates. But the most subtle, because Boolean mismatches can produce wrong results without any error message.

Your report runs. Your form works. The data looks fine. But the filtered results are silently wrong because True does not mean the same thing everywhere.

How Access Stores Booleans

Access Yes/No fields are stored as 16-bit integers: - True = -1 (all bits set: 1111111111111111 in binary) - False = 0 (all bits zero)

This is unusual. Most systems use 1 for True. Access uses -1 because of a historical convention from BASIC, the programming language that VBA descends from. In BASIC, the NOT operator is a bitwise NOT, and NOT 0 (flipping all bits of a 16-bit integer) produces -1.

How Server Databases Store Booleans

SQL Server: The BIT data type. True = 1, False = 0. SQL Server does not recognize -1 as a Boolean value.

MySQL: No native Boolean type. BOOLEAN is an alias for TINYINT(1). True = 1, False = 0. MySQL will accept any non-zero value as “truthy” but stores 1 for True.

MariaDB: Same as MySQL. BOOLEAN is TINYINT(1), True = 1, False = 0.

PostgreSQL: Native BOOLEAN type. True = TRUE (or 't', 'true', 'yes', 'on', '1'). False = FALSE (or 'f', 'false', 'no', 'off', '0'). PostgreSQL is the most flexible about what it accepts as input.

Where This Causes Bugs

Bug 1: Explicit Comparisons to -1

VBA code that checks for True by comparing to -1:

If rs!IsActive = -1 Then
    ' Do something for active records
End If

After migration to SQL Server, IsActive contains 1 for True. The comparison 1 = -1 is False. The code silently skips all active records.

Insidious variant: This code works correctly some of the time:

If rs!IsActive = True Then

In VBA, True is -1. So this comparison is 1 = -1, which is False. The code fails even though it looks correct.

Fix: Use one of these patterns:

If rs!IsActive Then                  ' Works everywhere
If CBool(rs!IsActive) Then           ' Works everywhere
If rs!IsActive <> 0 Then             ' Works everywhere

Bug 2: Queries with Boolean Criteria

Access queries use True/False in criteria:

SELECT * FROM Customers WHERE IsActive = True

In Access, this translates to WHERE IsActive = -1. When this query runs against a linked SQL Server table, Access translates it correctly to WHERE IsActive = 1. This is one case where Access handles the translation properly.

But if you write the query differently:

SELECT * FROM Customers WHERE IsActive = -1

Access does not translate the -1. It sends WHERE IsActive = -1 to SQL Server. Since no records have -1 in the BIT column, zero records are returned.

Fix: Use True/False in query criteria, never -1/0.

Bug 3: Sum and Count on Boolean Fields

In Access:

SELECT Sum(IsActive) FROM Customers

If 100 customers are active, this returns -100 (because True = -1, and -1 * 100 = -100). Access developers learn to use Abs(Sum(IsActive)) or -Sum(IsActive) to get the positive count.

After migration to SQL Server:

SELECT SUM(IsActive) FROM Customers

This returns 100 (because True = 1). Any code that negates the sum or takes the absolute value now produces the wrong result.

Fix: Use COUNT with a filter instead of SUM on Boolean fields:

SELECT COUNT(*) FROM Customers WHERE IsActive = True

This works correctly in both Access and server databases.

Bug 4: Checkbox Display

Access forms display Yes/No fields as checkboxes. When linked to a SQL Server BIT column, the checkbox display works correctly — Access properly interprets 1 as checked and 0 as unchecked.

But if you have a text box displaying a Boolean field (sometimes used in continuous forms or reports), the displayed value changes from “Yes”/“No” (or “True”/“False”) to “-1”/“0” in Access, and “1”/“0” from the server. Users may notice the inconsistency.

Fix: Use the Format property on the control to display “Yes/No” or “True/False” instead of the raw value.

Bug 5: VBA Boolean Variables vs. Database Values

VBA Boolean variables use the Access convention: True = -1, False = 0. This does not change when you connect to a server database. The mismatch exists between VBA’s internal representation and the values stored in the database.

Dim active As Boolean
active = True       ' active is -1 in VBA
rs!IsActive = active ' Sends -1 to SQL Server

' SQL Server BIT column stores 1 (any non-zero becomes 1)
' This works because SQL Server converts non-zero to 1
' But reading it back:
active = rs!IsActive  ' active becomes -1 because VBA converts 1 to True (-1)

In this case, the round trip works because VBA and SQL Server both perform conversions. But if you store the database value in an Integer variable instead of a Boolean:

Dim active As Integer
active = rs!IsActive  ' active is 1 (from SQL Server), was -1 (from Access)
If active = -1 Then   ' FAILS after migration

Fix: Always use Boolean variables for Boolean data. Never use Integer or check for -1.

The Complete Fix Checklist

  1. Search VBA code for -1: Find every comparison to -1 and replace with True, <> 0, or a bare Boolean check.

  2. Search VBA code for = True: In VBA, = True compares to -1. Replace with <> 0 or a bare Boolean check (If variable Then).

  3. Search queries for -1: Replace WHERE field = -1 with WHERE field = True or WHERE field <> 0.

  4. Review Boolean aggregations: Replace Sum(BooleanField) with COUNT(*) WHERE BooleanField = True.

  5. Check report calculations: Any report that sums or averages Boolean fields will produce different numbers after migration.

  6. Test conditional logic: Any If/Then or Select Case that branches on Boolean values needs verification.

Why This Matters

The -1 convention is unique to Access and VBA. Every other programming language, every other database, and every other data format uses 1 or TRUE for True. When you migrate away from Access, you are moving from the exception to the rule.

The fixes are straightforward — mostly search and replace. But you have to find every instance. A single missed -1 comparison can silently filter out records, skip business logic, or produce wrong calculations. Test every form, every report, and every VBA procedure that touches a Boolean field.