Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Handle Null Values in Check Boxes (Access/SR1)

    I apologize if this has been handles before. I searched for the issue, and couldn't find it. I am looking for a way to print a report with blank check boxes in a group if the group value is NULL, but checked Yes or No if the check box has a yea or no value.

    I have created a database of forms to track issues. We input every issue into the database, and print a form for each issue. Some (very few) people use the database to close the issue. Most use the printed form. To be as efficient as possible, I have created two tables--tblBlueCardInitiate and tblBlueCardComplete--the first to contain most of the information needed to describe the issue, and the second to contain most of the information to close the issue. Part of closing the form is to answer a series of Yes/No questions, so tblBlueCardComplete contains a number of Yes/No fields. I have created two keys in each--year and card number--and have created a 1-to-1 relationship with referential integrity. I have created a query to combine the two tables for printing the form with all the information from initiation and completion to include all records from tblBlueCardInitiate and any records from tblBlueCardComplete with matching year and card numbers. In the query, if there is no matching record in tblBlueCardComplete to tblBlueCardInitiate, the value of the check boxes (one of which is named BPP1) is NULL. I've created the report rptBlueCard to show the results of the query, using option groups to show the value of the Yes/No fields. If I print a Blue Card where someone has closed the card, and therefore assigned a value to BPP1, the option group displays correctly. However, if I print a card where someone has not closed the issue, the option group displays a No answer. In VBA, BPP1 has a value of 0, rather than NULL. I was hoping to use the NULL value to uncheck both boxes so someone completing the form by hand has an empty check box to check.

    This has frustrated me for a week, and I would appreciate any help anyone can offer. I have attached a much smaller version of the database, complete with tables, relationships, queries, and report if it will help. Thanks in advance.

  2. #2
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Handle Null Values in Check Boxes (Access/SR1)

    Checkboxes have a TripleState property. If it's set to True (or to "Yes" if you're using the Property Sheet), it causes the checkbox to display Null values differently from Yes or No. If you haven't looked at that setting yet, it may be that it will enable you to get the result you want (or something close to it), although I don't have any experience with printing forms.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Handle Null Values in Check Boxes (Access/SR1)

    An empty checkbox represents "no" or false, a checked checkbox represents "yes" or true. Null checkboxes show up as gray if you the triplestate enabled.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handle Null Values in Check Boxes (2000/SR1)

    Thanks for the quick replies. I looked into the TripleState, but according to the Help system, TripleState only applies to individual check boxes. In fact, the help system specifically excludes option groups. The intent of the Yes/No fields in my database is to only allow a yes or no answer, but since only 5% or so of the forms are completed within the database and not on paper, I need to be able to supply an empty check box for the paper form. I don't mind using something other than an option group and using VBA to set the checked property, but everything I've tried doesn't work, because the report rptBlueCard is returning a value of 0 (False) for a record in tblBlueCardComplete that doesn't exist, rather than the more accurate NULL. For this reason, IsNull([BPP1]) doesn't work. It does return NULL in the query qryBlueCard, however, which is part of my confusion.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Handle Null Values in Check Boxes (2000/SR1)

    You failed to mention that your "checkboxes" were in option groups. Those are not checkboxes, they are option button formatted to look like checkboxes, which is an entirely different thing. Option groups don't behave the way you're trying to make them behave. An option group has a value, but you can assign it a value of zero as a default and then make each of the buttons in the option group have a value higher than zero so that if none of them are selected, a zero value will tell you that the question has not been answered. I believe that will give you the effect you're looking for.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handle Null Values in Check Boxes (2000/SR1)

    Still, my question remains: how do I return the NULL value from the query? Again, the query (qryBlueCard in the example I attached earlier), combines the initiated issue table (tblBlueCardInitiate) with the completed issues from the table tblBlueCardComplete. The related fields are the date and card number, joined 1-to-1. The query, qryBlueCard, is set up with an inner join to include all the issues from tbleBlueCardInitiate and related fields from tblBlueCardComplete. In this query, any record that doesn't exist in tblBlueCardComplete returns NULL values. It doesn't matter whether those values are text, integer, or Yes/No. For some reason, though, the same fields from these record--BPP1 in the attached example--return a value of zero (0) instead of NULL. I need a way to tell the difference between a true NULL and a zero (False) in the report so I can display it properly.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Handle Null Values in Check Boxes (2000/SR1)

    That isn't what I'm seeing in your query. When I run it, it returns one true (checked), one false (unchecked) and one null (gray). However, you can't bind a true false field to an option group unless you make one of the options True (non-zero) and one false (0), but then you have no way to display a null.
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Handle Null Values in Check Boxes (2000/SR1)

    In a report, an indeterminate value for a Yes/No field makes no sense, so undeteminate values of BPP1 are resolved as the default value of a Yes/No field, i.e. False. I don't think there is a way around this, except perhaps changing BPP1 to a number field.

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handle Null Values in Check Boxes (2000/SR1)

    Thanks, Charlotte and Hans. Charlotte, you're correct that my query returns a True, a False, and a NULL. It doesn't matter to me whether I use and option group or not to display the data; I can format it using VBA. However, the NULL value in the query becomes a False in the report. Hans, you're correct about the option group as well. I've tried several techniques to try to return the value of the NULL BPP1 in the report as a null in BPPValue, but even putting an Nz() or an IIf() in the query to return a 1 for a NULL value returned a False instead.

    Here's where my true ignorance of Access comes in. Using Nz([BPP1],1) in the query returns a 1, but returns a 0 in the report. Evidently, then, the recordset is different from within the query versus the report. Based on this theory, I ran a make table query, then ran the report on the new table. This time, the report looks just like I want it.

    On one hand, I'm glad I can get the data to display correctly, but this seems like an awful lot of overhead, since I split the two tables to save space in the first place!

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handle Null Values in Check Boxes (2000/SR1)

    OK, more information in the hopes someone can tell me what I'm doing wrong. This is driving me nuts! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    If you look at the example I've attached, I can run my make table query to join the tables, using CInt(Nz(tblBlueCardComplete.BPP1),1) to make a new table with a 1 for every record in the table tblBlueCardComplete that doesn't exist for a record in the table tblBlueCardInitiate. From there, the report rptBlueCard works exactly like I'd want it to.

    However, the reason I split the tables to begin with was to save disk space. The old databse is over 8.5MB (compacted on each exit), so you can see why I want to do what I can to reduce file sizes.

    The new twist is that in the example I've attached with only a handful of records, the make table query works. But in my working database with nearly 600 records, the same function CInt(Nz(tblBlueCardComplete.BPP1),1) returns a zero, instead of 1! Where am I going wrong?!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Handle Null Values in Check Boxes (2000/SR1)

    If it works OK in the attached database, we can't test where it goes wrong, can we?

    I would experiment with changing BPP1 to a number (integer or long integer) field. You can use a combo box with values -1 (Yes) and 0 (No) to force the user to enter only what amounts to Yes/No values. The query should work OK in the report too then.

    See modified version of the database you attached to the first post in this thread.

  12. #12
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handle Null Values in Check Boxes (2000/SR1)

    I made the changes you proposed, Hans, and it worked. It even solved my report formatting problem. I still don't understand how a query can show one value using a function then a report based on the same query treats the value as a zero even if the query value is one, but it's results that count. Thanks for the help.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Handle Null Values in Check Boxes (2000/SR1)

    Apparently, Access evaluates Yes/No fields in a query differently if the query acts as record source for a report. Numeric fields are treated the same way whether the query is used in a report or not.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •