0

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

Kronos1A9
  • 1
  • 1
  • since you are using a LEFT JOIN are the nulls present where no record was joined? – Brad Sep 13 '16 at 17:48
  • 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 Sep 13 '16 at 18:09
  • 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 Sep 13 '16 at 18:14

1 Answers1

0

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:

enter image description here

Community
  • 1
  • 1
Rominus
  • 1,181
  • 2
  • 14
  • 29
  • Good advice, thank you. I might just use a drop down box with yes or no as the options to avoid this issue all together. The strange part about this is there are two other Yes/No check boxes in the query and they don't produce the same error. Also, prior to the JOIN, the SponsorPackage field doesn't show any null values, only true/false. I even made sure to make the default value of it to NO. When I get time tomorrow I will post the code. Maybe there is something that I am missing that would cause this. – Kronos1A9 Sep 13 '16 at 15:58
  • I tried your advice on my example, using Yes or No in a text field instead of the check box. I randomly populated the table with Yes and No, after running the query, all of the No's disappeared as though they were also null. Beginning to think the issue isn't with the check boxes at all. – Kronos1A9 Sep 13 '16 at 16:54
  • @Kronos1A9 yeah, if the text field is coming back null there's something else going on. One thing to check for is any data processing or calculation that uses another variable that is null; calculations involving nulls can return null instead of behavior you'd expect from a blank value. – Rominus Sep 13 '16 at 19:06