Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: Access 2003 : How to show all records if "Criteria" left blank

    Hello all, In Access 2003
    We use form with Text boxes and Combo boxes to assign data to the "criteria" in Querries.
    But I would like to make the Querry show all records if the Text box or Combo boxes is left blank.
    Thank you for any suggestions.
    Derek

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Derek

    Welcome to the forum.

    A few solutions:
    You can build your query dynamically (Izy has posted on this many times).
    You can use something like
    Code:
    WHERE NZ(MyField, "") LIKE Forms!MyForm!MyCombo & "*"
    or
    Code:
    WHERE (MyField = Forms!MyForm!MyCombo or Forms!MyForm!MyCombo IS NULL)
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2006
    Location
    MI, USA
    Posts
    14

    Exclamation Show all based on no criteria

    What I do is in the query's criteria section I will put something like this:

    Like IIf(ISNull([forms]![YourForm_frm]![Criteria_txt]),"*",[forms]![YourFrom_frm]![Criteria_txt])

    It is easy to understand if you work from the right to the left.
    On the right side we basically have an IsNull statement and we are combining that with an IIF statement so we are saying, if Criteria_txt "ISNULL" then show all the files that are LIKE "*" (which is the wild card for everything) otherwise show all the files that are LIKE [Criteria_txt].

    I hope this helps.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hey Dude

    Nice alternative
    One minor problem - Null is not LIKE "*" so Nulls will never be returned.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2006
    Posts
    3
    To Pootle Flump and Ferris_Dude,.
    thank you both...
    I tried the option from Pootle Flump with ...[][][] OR [][][] IS NULL..
    and also tried the one from Ferris, they both worked..
    I'm really happy to have found someone to help..
    thank you guys..!!!!
    I hope you wont mind helping out in future..!
    Thanks again to all...
    DerekH

  6. #6
    Join Date
    Mar 2014
    Posts
    1
    "pootle flump Hey Dude

    Nice alternative
    One minor problem - Null is not LIKE "*" so Nulls will never be returned."

    Hello pootle flump is right, Can someone please suggest an alternative?

    Many Thanks

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Pootle did, almost 8 years ago.
    Have a nice day!

  8. #8
    Join Date
    Aug 2006
    Posts
    3
    To Sinndho and Pootle..... Yes my querry was answered already 8 years ago... I hope I said THANKS to all concerned at that time. We have been using their advice ever since..!!
    Maybe I need to post a new question asking HOW on earth did my querry pop up again after so much time..!!??

    Thanks again to all..
    Derek

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by DerekHewson View Post
    HOW on earth did my querry pop up again after so much time..!!??
    Baring hardware failure and other acts of God, DBForums tries to keep non-spam threads around forever.

    An amazing percentage of our visitors hit exactly one page, without ever posting. This implies that they are arriving via a search engine (like Google) and leave satisfied with the answer that they find.

    This is great for the users, but a little tough for our revenue model. The advertising on our site is what pays to keep DBForums here, the lights on and disks spinning. For those who visit a single page and move on, you're welcome. For those who visit DBForums occasionally and allow the ads to run, thanks for helping keep us on the internet!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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