Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check box input in table (Access 2000)

    I want the output of a check box on a form to appear in the connected table as "Yes" or "No" It is appearing as "-1" Is there a property that would fix this? The data type of the field in the table is text. I tried setting it to "Yes/No" but that gave me boxes in the fields and I don't want that because I can't filter on it. I would prefer a solution that doesn't require VB code.
    Thanks in advance.

  2. #2
    New Lounger
    Join Date
    Jan 2001
    Location
    Vermont
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    The rest of the answer is kind of involved, but the quick answer is that you can filter on Yes/No's by filtering on True or False

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

    Re: Check box input in table (Access 2000)

    Checkboxes can only be bound correctly to Yes/No fields, which means that their values are True (-1), False(0) or Null. The display format in the table is irrelevant, since you don't normally show the table to users, you use a form or a query instead. If you try to bind a checkbox to a text field, it won't function properly anyhow, because while it might insert a value into the field, it won't be able to properly display the value after that.
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    Hi Charlotte

    How would have thought.

    "Checkboxes are True (-1), False(0) or Null. "

    From an operational standpoint can the operator tell the difference between False and Null?

    Interested, John

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

    Re: Check box input in table (Access 2000)

    Oh, yeah. Yes/No fields are tri-state by default and the controls bound to them reflect that. If a checkbox has been clicked, you either see a check or an empty checkbox. If there is no default value or the value has been set to Null, the checkbox is gray, indicating the null value.
    Charlotte

  6. #6
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    Thanks, Charlotte. That makes sense, and I don't really care what the table looks like.
    Now I am trying to make a report that counts the ID of the individual if the check box = True. IIf doesn't work because there is no false condition (=IIf (qryOne!CkBox=True, Count(qryOne!ID#), ???). Would I have to do this in VBA?

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

    Re: Check box input in table (Access 2000)

    The way you usually do it is to Sum the field (since it should contain zeros and -1s) and use the Abs() function to turn it into a positive number. Where are you trying to do this, in the query itself or in a report?
    Charlotte

  8. #8
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    I have a report rptMainFile based on a table tblMainFile. In the footer of this report I put an unbound text box and in the Properties, I use the Expression Builder to enter the formula =Sum([tblMainFile]![CM PCP]). CM PCP is the field with the check boxes. The objective is to determine the number of records that have a check in the box in that field.
    What I get is an error message: #Error. I think that somehow the formula cannot see the field it is trying to sum.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    Your field [CM PCP] has to be in the detail of the report. If you don't want to print it set the visible property to No.
    In you Unbound textbox use:
    <pre>=Abs(Sum([CM PCP]))</pre>

    Francois

  10. #10
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    Yes, Francois, that is true, but if it is in the detail of the report, it appears multiple times, and I want it to appear only once as a numerical summary of the field. Do you know a way to do this?

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    If you set the visible property of the checkbox [CM PCP] to No, it will not bee printed.
    The visible property of the unbound textbox should be set to Yes.
    Francois

  12. #12
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    Okaaaay! I put in report header and footer, took out the page header and footer and put the calculations in the footer. I get one page with the summary totals.

    Thanks to Francois and Charlotte.

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Check box input in table (Access 2000)

    I've been doing this for about 60 dog years and had the same problem yesterday - it took me about 45 minutes to remember you can put the sum in the page footer - it has to go in the report footer. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Wendell

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check box input in table (Access 2000)

    And every time it's more difficult to find the error because "you have done this so many times"
    Francois

Posting Permissions

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