Results 1 to 12 of 12
  1. #1
    Rujahin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    Washington
    Posts
    15

    Multiple checkboxes in a query to return ALL records

    I have a form called [frmCriteria] that has a few text boxes and four check boxes [speeds],[tcounts],[manual],[other]. My query [qryCriteria] has 7 fields from the table [tblTrafficCount]. Query fields are [CountID],[SR],[MP],[SpeedStudy],[TCount],[ManualCount],[Other].


    What my co-worker wants to happen is to click any single or combination of boxes and have ALL the records show on the report. So if all the boxes are checked, all the records would show.
    In other words, if just selecting [speeds] returns 50 records, and just selecting [tcounts] returns 75 records, and only 11 records have both boxes checked, I need to show all 125 records. Hope this explains it well enough.

    I've been trying varying forms of IIf statements but haven't gotten the correct results.
    Any help would be appreciated.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    how are you returning your results, if it's in a subform you'll either have to set a filter (which I hate doing) or modify the recordsource


    In terms of building a query you want to use the OR function.

    In your query design in your SPEEDS field you have a -1 as a criteria.
    If you are looking for anyone who has a SPEEDS or a TCOUNTS criteria of -1 your first line would have -1 under SPEEDS your second criteria line (First OR) under TCOUNTS your criteria would be -1

    In terms of a SQL statement it would be something like

    SELECT CountID, SR, MP, SpeedStudy, TCount, ManualCount, Other FROM tblTrafficCount WHERE SPEEDS = -1 OR TCOUNTS = -1

  3. #3
    Rujahin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    Washington
    Posts
    15
    Results are returned on a form. no subforms or anything. When I try it your way, as long as all the boxes are checked all the records do show up (458). However, if only one box is checked, it doesn't work right. It gives some from every checkbox but not ALL for any of them. Im not very comfortable with sql but here is what it looks like.

    SELECT tblTrafficCount.CountID, tblTrafficCount.SR, tblTrafficCount.MP, tblTrafficCount.SpeedStudy, tblTrafficCount.TubeCount, tblTrafficCount.ManualCount, tblTrafficCount.OtherTypeStudy, (Date()-[countdate])/365.25 AS age
    FROM tblTrafficCount
    WHERE (((tblTrafficCount.SR) Like [forms]![frmcriteria].[sr]) AND ((tblTrafficCount.MP) Between [forms]![frmcriteria].[startmp] And [forms]![frmcriteria].[endmp]) AND ([Forms]![frmCriteria]![Speeds]=-1) AND (((Date()-[countdate])/365.25)<=[forms]![frmcriteria].[age])) OR (((tblTrafficCount.SR) Like [forms]![frmcriteria].[sr]) AND ((tblTrafficCount.MP) Between [forms]![frmcriteria].[startmp] And [forms]![frmcriteria].[endmp]) AND ([Forms]![frmCriteria]![Tubes]=-1) AND (((Date()-[countdate])/365.25)<=[forms]![frmcriteria].[age])) OR (((tblTrafficCount.SR) Like [forms]![frmcriteria].[sr]) AND ((tblTrafficCount.MP) Between [forms]![frmcriteria].[startmp] And [forms]![frmcriteria].[endmp]) AND ([Forms]![frmCriteria]![Manuals]=-1) AND (((Date()-[countdate])/365.25)<=[forms]![frmcriteria].[age])) OR (((tblTrafficCount.SR) Like [forms]![frmcriteria].[sr]) AND ((tblTrafficCount.MP) Between [forms]![frmcriteria].[startmp] And [forms]![frmcriteria].[endmp]) AND ([Forms]![frmCriteria]![Other]=-1) AND (((Date()-[countdate])/365.25)<=[forms]![frmcriteria].[age]))
    ORDER BY tblTrafficCount.MP;

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Something's wrong with your criteria.

    When you check SPEEDS on your form (forms!frmcriteria!speeds) I assume you are checking something in the SPEEDSTUDY field, but what, is it anything that has a value? does the checking the checkbox on frmcriteria mean that you are looking for any record with a value in that field:

    SPEEDS -> SPEEDSTUDY
    TUBES -> TUBECOUNT
    MANUALS -> MANUALCOUNT
    OTHER -> OTHERTYPESTUDY

    If so you'd want a query more like this:

    Code:
    SELECT tblTrafficCount.CountID, tblTrafficCount.SR, tblTrafficCount.MP, tblTrafficCount.SpeedStudy, tblTrafficCount.TubeCount, tblTrafficCount.ManualCount, tblTrafficCount.OtherTypeStudy, (Date()-[countdate])/365.25 AS age, IIf([forms]![frmcriteria]![speeds]=-1 And Not IsNull([speedstudy]),1,IIf([forms]![frmcriteria]![tubes]=-1 And Not IsNull([tubecount]),1,IIf([forms]![frmcriteria]![manuals]=-1 And Not IsNull([manualcount]),1,IIf([forms]![frmcriteria]![other]=-1 And Not IsNull([othertypestudy]),1,0)))) AS ShowSpeed
    FROM tblTrafficCount
    WHERE (((tblTrafficCount.SR) Like [forms]![frmcriteria].[sr]) AND ((tblTrafficCount.MP) Between [forms]![frmcriteria].[startmp] And [forms]![frmcriteria].[endmp]) AND (((Date()-[countdate])/365.25)<=[forms]![frmcriteria].[age]) AND ((IIf([forms]![frmcriteria]![speeds]=-1 And Not IsNull([speedstudy]),1,IIf([forms]![frmcriteria]![tubes]=-1 And Not IsNull([tubecount]),1,IIf([forms]![frmcriteria]![manuals]=-1 And Not IsNull([manualcount]),1,IIf([forms]![frmcriteria]![other]=-1 And Not IsNull([othertypestudy]),1,0)))))=1))
    ORDER BY tblTrafficCount.MP;

  5. #5
    Rujahin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    Washington
    Posts
    15
    still doesn't quite work. think it getting closer tho. here's a condensed version of the DB. Thanks for the help so far.
    Attached Files Attached Files

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you're listed as using Access 2010 which I can not open unless you convert it or your display version is different than that of this db I can't look at it. You still haven't said what your criteria are if the checkboxes are marked. Do you want to show everything if it's checked or only items with a value in them or...?

  7. #7
    Rujahin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    Washington
    Posts
    15
    Ahh. Sorry. Ok, if I have [speeds] and [tubes] checked on [frmCriteria], I want to have the query give me ALL the records that have [tbltrafficcount]![speedstudy] checked AND all the records that have [tbltrafficcount]![tubecount] checked.
    Is this more of what your asking?

  8. #8
    Rujahin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    Washington
    Posts
    15
    Ok. here's a 2003 version. Hope it works.
    Attached Files Attached Files

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok on your table

    SPEEDSTUDY
    TUBECOUNT
    MANUALCOUNT
    OTHERTYPESTUDY

    are all stored as yes/no fields.

    So in your query instead of checking for a NULL field you need to check for a field with a -1 instead (I was guessing at your data type for my example). The parts of your SHOWSPEED would look like:

    IIf([forms]![frmcriteria]![speeds]=-1 And [speedstudy] = -1,1, <continue iif statement>...)

  10. #10
    Rujahin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    Washington
    Posts
    15
    YES! this is exactly what i want. Thank you. For some reason the [age] function is leaving out a bunch of records tho. If I take the [age] function out, everything works perfectly (returns 190 records with maunal count selected and 20 for age), with [age] in, it returns 125 records. Any Ideas?
    Last edited by Rujahin; 04-15-2013 at 11:31 AM. Reason: a little more description

  11. #11
    Rujahin is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Location
    Washington
    Posts
    15
    Nevermind. Forgot to set the format of Age box on frmCriteria. Everything works great. Thanks so much for all the help.

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Your AGE is a calculated value (today's date minus count date)/365.25

    You also have these fields in your query:

    CountID
    SR
    MP
    SpeedStudy
    TubeCount
    ManualCount
    OtherTypeStudy

    this means that if you have these two sets of data:

    Code:
    CountID  SR  MP  SpeedStudy  TubeCount  ManualCount  OtherTypeStudy
    1        AA  BB  -1         0          0            0
    2        AA  BB  -1         0          0            0
    You're going to get 2 records in your query because you're including ALL data from your table.

    If you want to show a number of counts by everything except the count ID you'd have to make this an AGGREGATE query and make the CountID field a COUNT total (look for the sigma button on your toolbar looks like a capital E) Then, assuming the countdate was the same for both you'd get something like

    Code:
    CountofCountID  SR MP ---> remainder of fields
    2               AA  BB -----> remainder of fields
    I don't know how different your actual query is from my example but if you are excluding the countID from your query and still getting more records when you have the age calculation is means that you have a set of identical data that only differs on the COUNTDATE fields and it's producing different values for AGE for those two COUNTDATEs. If you are trying to get an integer value for that age calculation and group on that you'd want INT((Date()-[countdate])/365.25) as your age calcuation.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-16-2012, 08:32 PM
  2. Replies: 0
    Last Post: 03-06-2011, 04:10 AM
  3. Filter records based on multiple checkboxes
    By kbremner in forum Forms
    Replies: 2
    Last Post: 01-18-2011, 10:59 AM
  4. Return all records from Query
    By ysrini in forum Queries
    Replies: 1
    Last Post: 01-15-2010, 09:52 PM
  5. Replies: 0
    Last Post: 08-04-2009, 08:51 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