Results 1 to 3 of 3
  1. #1
    JynxRD is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    1

    How to Query fields with check boxes?

    I'm creating an attendance tracking database. I have a table called Attendance that has Employee Name, Tardy and Absent. The Tardy and Absent fields are check boxes, some checked, some not checked at all. I'm trying to create a Query to just give me the count for each person, how many tardy are in that record for that Employee Name and so on.



    What I did was this:
    Field: Tardy
    Table: Attendance
    Total: Count
    Sort: (Blank)
    Show: (checked)
    Criteria: Abs(DSum("Tardy","Attendance"))


    Now what this does is simply count the boxes regardless of if there checked or not. How can I make this show the sum of only those that are checked true.
    I been reading before asking and I was reading I could do something like = Count(IIF([Tardy] = True,1,Null)) but even though Tardy only exist on one table and I have specified that table it still tells me that Tardy could apply to too many things when I try and run it.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Try:
    Field: Tardy
    Table: Attendance
    Total: Sum
    Sort: (Blank)
    Show: (checked)
    Criteria: -1
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    oldman is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    17
    It is really quite simple. A check will be stored as a -1 value in the underlying table and a non-checked box will be stored as a zero. If you want to find all records with a check, simply use a query that says the field value of interest has a value of -1. If you use the query design module, put a -1 criteria in the column that contains the field name of the field where you want a check box to be checked. If you want to know the count of unchecked records, use a check value of zero in the criteria box.
    For all of you advanced people, sometimes the simplest things are not obvious to a newcomer to Access. I am presently teaching a new person to take over databases that I have written and am sometimes surprised at what is not obvious to a new person. We all seem to assume too much prior knowledge for a real database newbie.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Queries Based On Check Boxes
    By Rubz in forum Queries
    Replies: 7
    Last Post: 05-07-2010, 03:46 PM
  2. Yes/No check boxes
    By Desstro in forum Forms
    Replies: 2
    Last Post: 05-03-2010, 04:26 AM
  3. To check or Un-Check all Boxes in a form
    By devcon in forum Forms
    Replies: 7
    Last Post: 05-01-2010, 12:03 AM
  4. Un-Check all Boxes
    By cotri in forum Forms
    Replies: 4
    Last Post: 04-30-2010, 12:53 PM
  5. check boxes
    By chiefmsb in forum Forms
    Replies: 1
    Last Post: 11-14-2006, 02:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums