Access query with join returns checkbox as null value

Question

This is very strange even for my limited knowledge of Access. Background information: I am making a database which track the people I work with. The boss wants a report with the people leaving and the people replacing them, so I though this may work. However when I join the information, it results in the checkbox showing a NULL value. I made sure to set the default to NO and it is not set up for a triple state checkbox from what I can tell.

This is just a sample of what it would look like:

Table

I am using the fields IDReplicate and Replacement ID in order to JOIN the table and query. IDReplicate is just a copy of the Primary Key. Using the primary key as the first field in the JOIN produces a type mismatch

My query to pull information about the replacement people:

SELECT tblExample.IDReplicate, tblExample.Arrival, tblExample.City, tblExample.Package, tblExample.LName FROM tblExample;

My query to which joins the people leaving and people arriving:

SELECT tblExample.LName, tblExample.Departure, tblExample.ReplacementID, qryReplacement.* FROM tblExample LEFT JOIN qryReplacement ON tblExample.ReplacementID = qryReplacement.[IDReplicate];

Resultant Query Datasheet

since you are using a LEFT JOIN are the nulls present where no record was joined? — Brad
It appears that way, if I try to use a RIGHT JOIN, the results are opposite and I lose the names and departure dates. It appears it is the JOIN property that is causing my error. I would use an inner join, but then I don't retain any of the people departing that have yet been assigned a replacement. Do you know of any other way to get the same results without using JOIN? In other words, build a table or query that shows each individual departing, and if they have a replacement with their info in the same row? — Kronos1A9
You could wrap qryReplacement.Package in Nz like Nz(qryReplacement.Package,false) then it should display as 0 for those null values. You'll lose the checkbox look. but that' probably fine. You can get it back if you make your report an actual Report object (you could do the same on a form bound to the query). — Brad

Rominus Rominus · Accepted Answer · 2016-09-13T15:25:06

It sounds like you may have a three-state checkbox where you want a two-state, check this SO post for details:

Determine whether a Access checkbox is checked or not

IMO, checkboxes are trouble and best avoided. My advice would be to change the field to a text field with a constraint to limit entry to yes or no, defaulting to no. That way you know exactly what data is really in your table, instead of counting on Access to translate an interface element into data. If you do it in the Access table design interface it would look something like this:

Access query with join returns checkbox as null value

1 Answers