Results 1 to 10 of 10
  1. #1
    GaryPanicZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    25

    using tick box as a filter if unticked - x if ticked all

    Hi guys (I have lost my password tomy orginal loggin - so have set up new one ...



    what I am trying to do is

    table 1 (account ) if ticked -1

    now I have a report that filters only those that are ticked (fine)
    what i want is to tick on my form to override and give me all (both-1 and 0)




    IIf([forms]![SummaryReportsfrm]![trigger]=True,-1,0)
    I thought
    IIf([forms]![SummaryReportsfrm]![trigger]=True,-1, -1 or 0) would work but it does not ..
    plot lost - how do I filter /not filter ?

    help would be greatly appricated
    G

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    IIf([forms]![SummaryReportsfrm]![trigger]=True,-1,0)
    you don't need the iif, boolean true/yes=-1, boolean false/no=0

    in your query try

    WHERE trigger or not [forms]![SummaryReportsfrm]![trigger]

    or if you want the extra typing

    WHERE trigger= true or [forms]![SummaryReportsfrm]![trigger]=false

  3. #3
    GaryPanicZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    25
    ok thanks for this - however this will give me either those ticked -1 ( great ) or those not ticked 0
    what i want is those ticked and ( all- ticked and unticked )

    so on my form Summaryreports I have a tick box unbound (trigger) - if this is ticked apply the filter
    ie only show ticked items
    if its not ticked then show all


    (or the otherway round if unticked only show ticked items -which is how I currently have it - but more than happy to flip it the other way round)

    not bother which way it works either ticked and show ticked items -

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I understand what you want, it is a common question. suggest try it - but you may need to include the not (or not!) depending on what you want to happen when the form trigger is true


  5. #5
    GaryPanicZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    25
    Not quite getting it -I will upload screengrab
    Attached Files Attached Files

  6. #6
    GaryPanicZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    25
    uploaded

    MonitorYN

    this has the -1 and 0
    -1 being true and 0 being false
    so my report
    would have
    -1 to get just the records required - works (I am not that dumb - although today could be the exception ...!)
    its the -1 and 0 that I am not quite getting my head around (it could be the reasonable weather)

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in the criteria row for the trigger column put

    True OR [forms]![SummaryReportsfrm]![trigger]=false

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is how I do it:
    Click image for larger version. 

Name:	TrueOrFalse.PNG 
Views:	15 
Size:	6.7 KB 
ID:	41900
    I add a field to the query for the checkbox on the form and use two of the criteria rows,

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    GaryPanicZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    25
    OK works
    but I don't understand why ??? - must be magic -
    and it's that bit that will freak me out - it will come to me at about 2 in the morning
    Many thanks Ajax
    and also Gicu - and this one makes a little bit more sense but i like the tidiness of Ajax version - i just need to understand it ..
    well its nearly the weekend - and that means cider/beer time

    hope all of you are keeping well and keep safe
    many thanks again
    (now where the button to say solved ?? )

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    but I don't understand why ???
    thought I'd posted an explanation but can't see it now



    all criteria does is return true or false for each condition (applies to string comparisons, >, < etc). Either something is true or it's not, there is no maybe.

    so you have two records one where trigger is true, and one where it's not (false)

    your form trigger is ticked - I'm assuming meaning 'show triggered records if ticked'

    using an AND between them (i.e. in query grid they are on the same row) means all conditions must be true

    true and false will return false

    using an OR between them (i.e. in the query grid they are on different rows) means only one condition needs to be true

    if form is not ticked (show all records)

    WHERE trigger or not [forms]![SummaryReportsfrm]![trigger]
    rec1 true or not false (true) = true - return record
    rec2 false or not false (true) = true - return record


    if your form is ticked (show 'triggered' records)

    WHERE trigger or not [forms]![SummaryReportsfrm]![trigger]
    rec1 true or not true (false) = true - return record
    rec2 false or not true (false) = false - do not return record



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

Similar Threads

  1. Auto Date when checkbox ticked
    By 2410Davie in forum Access
    Replies: 12
    Last Post: 02-22-2016, 01:52 AM
  2. how to add tick boxs to filter
    By sspreyer in forum Programming
    Replies: 1
    Last Post: 11-05-2013, 09:13 AM
  3. Replies: 4
    Last Post: 06-24-2013, 03:36 AM
  4. If Checkbox is ticked, Data Must be entered
    By DTK0902 in forum Access
    Replies: 8
    Last Post: 12-01-2012, 04:32 PM
  5. Open report if checkbox is ticked
    By Patience in forum Reports
    Replies: 3
    Last Post: 06-23-2010, 08:34 AM

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