Results 1 to 5 of 5
  1. #1
    mmmich00 is offline Novice
    Windows 8 Access 2007
    Join Date
    Dec 2015
    Posts
    3

    How do I tell access that when unchecked to return ALL records?

    I'm very new to access and need some help with a search form. My database is used to track cargo information arriving and departing on trucks. On my search from there is a checkbox that indicates if the truck has Hazardous material(HazMat) or not. If checked my query returns all the records with hazmat. if unchecked it returns all records without Hazmat. How do I tell access that when unchecked to return ALL records?

    My query criteria is =[forms]![Frm_InboundTruckSearch]![HazMat].
    My table is "Tbl_InboundTruckLog"
    Search form is "Frm_InboundTruckSearch"


    The Feld on the Search from and table is "HazMat"

    I hope this is enough info for someone to tell me what I'm doing wrong. Again I'm really new to access so I'm hoping this is an easy fix I just don't see.

    thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    My query criteria is =[forms]![Frm_InboundTruckSearch]![HazMat]
    It sounds like what you need is a criteria that returns records where the field in your form is equal to True. I suspect what you need is a criteria like the following ...

    Code:
    [forms]![Frm_InboundTruckSearch]![HazMat] = -1

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't use dynamic parameterized queries. I prefer VBA to construct filter string and apply to form or report. Review: http://www.allenbrowne.com/ser-62.html

    However, if HazMat is a Yes/No field, could set the control as a combobox with Yes & No & "" choices instead of checkbox and then this as parameter:

    LIKE [forms]![Frm_InboundTruckSearch]![HazMat] & ""

    Or maybe to keep the checkbox:

    LIKE IIf([forms]![Frm_InboundTruckSearch]![HazMat]=-1, -1, "*")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    mmmich00 is offline Novice
    Windows 8 Access 2007
    Join Date
    Dec 2015
    Posts
    3
    June7, the "LIKE IIF" worked perfectly. Thank you so much for your help and the reading. Really good stuff.

  5. #5
    mmmich00 is offline Novice
    Windows 8 Access 2007
    Join Date
    Dec 2015
    Posts
    3
    ItsMe, Thanks for the quick response. It didn't work the way I needed. But I appreciate your time and assistance.
    Last edited by mmmich00; 12-30-2015 at 08:12 AM. Reason: Forgot a name

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

Similar Threads

  1. Replies: 1
    Last Post: 01-02-2015, 10:07 PM
  2. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  3. Replies: 7
    Last Post: 11-08-2013, 03:34 AM
  4. Replies: 3
    Last Post: 08-06-2012, 04:59 PM
  5. Access not return the records
    By ashutoshNetsity in forum Access
    Replies: 1
    Last Post: 05-09-2010, 04:27 AM

Tags for this Thread

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