Results 1 to 15 of 15
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Access Queries and Nulls (2000)

    I want to be able to use a parameter in a query.
    If the Parameter is entered the Criteria must match that parameter
    If the Parameter is empty, the query should return ALL rows including those with nulls

    e.g If Prompt said Enter Status? then if a status is entered only matching rows are returned
    If NO value is entered, the query must return ALL rows with an entry in that field as well as those with a null.

    If I use a wild card statement : Like IIf([Enter Status] is null,"*",[Enter Status]) then leaving the field empty eliminates the Nulls
    If I use a statement such as : Like IIf([Enter Status] is null,"*",[Enter Status]) Or Null then nulls are returned, BUT only if a Status is entered
    If I use a statement such as : Like IIf([Enter Status] is null,"*",[Enter Status]) Or Like IIf([Enter Status] is null,Is Null,[Enter Status]) then Only Non Null rows are returned if the box is left blank

    Short of constructing the SQL in code from a form rather than trying to use parameters, is there a simple solution that I have just missed?

    Any help gratefully appreciated.......

    Andrew Walker
    Andrew

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Access Queries and Nulls (2000)

    This is from the Help Menu by typing "Return all records" in the Answer Wizard:

    I want the option of returning all records with a parameter query.

    In the design grid under the field that contains the prompt in the Criteria cell, type [prompt] Is Null in the Or cell, where prompt is the same prompt that is in the Criteria cell for that field. For example, in the CategoryID field:

    In this cell Type
    Criteria Like [Enter Category ID:]
    Or [Enter Category ID:] Is Null


    Now when you run the query, if you leave the Enter Parameter Value dialog box blank, the query returns all records.
    Easy Access

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Queries and Nulls (2000)

    You can simplify it like this: <pre><font color=blue>Like [Enter Status] & "*"</font color=blue></pre>

    or <pre><font color=blue>Like Nz([Enter Status],"*")</font color=blue></pre>

    Charlotte

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access Queries and Nulls (2000)

    Thanks, but I have already tried this. Unfortunately it also returms nulls when the Parameter is filled in.

    I need a query that rturns just those requested entries when the parameter is used, and ALL entries (inc Nulls) when the parameter is blank.
    Andrew

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access Queries and Nulls (2000)

    Yep, that is true, but it doesn't answer the question.
    If it was a simple case then I would have used the latter example.
    However, the requirement for this query is that it needs to:
    1. Return just those entries matching the Parameter entry (IF it is filled in)
    2. Return ALL records (inc) nulls only when the parameter is not there.

    Unfortunately Like with a wild card of * on a field returns only those entries that are not null. It behave exactly like the criteria of Is Not Null.

    This is why I was attempting to use a longer query.

    Note: Like nz([Enter Status],"*") or Is Null is no good either.
    This goes the other way and returns Nulls with both Blank and Non blank parameter entries.

    Any other ideas, short of using VBA to generate the exact query?

    Andrew
    Andrew

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Queries and Nulls (2000)

    <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> but I think you are going to have to make a decision from a form and run one of 2 queries that you have setup based upon the parameter that will have to be a form control and not a query parameter.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access Queries and Nulls (2000)

    This is kind of a bogus trick, but I could not find any good way to do this using the actual field and an IIf statement, kept getting an "Expression is too complicated" message when tried to run query. Use "Like" criteria for actual field. Then add a bogus calculated field to query, in my example I used Expr1: "ABC". Then use an IIf expression with this bogus field: if no criteria entered, IIf returns "ABC", so you get all records, since ABC = ABC; if criteria is entered, then IIf returns "XYZ", meaning no records will be returned by this field, because ABC does not = XYZ, but you will get the records that meet "Like" criteria for actual field. The SQL looks like this:

    SELECT TABLE1.FLD1
    FROM TABLE1
    WHERE (((TABLE1.FLD1) Like [Enter Criteria:] & "*")) OR ((("ABC")=IIf(IsNull([Enter Criteria:]),"ABC","XYZ")))
    ORDER BY TABLE1.FLD1;

    Note use of OR in WHERE clause; these 2 criteria are entered on separate lines in query design view. Try replacing the generic names used here with actual table & field names, this should work.

    HTH

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Queries and Nulls (2000)

    Modify the SQL statement dynamically (or create a temporary querydef object to use as the record source) based on the presence or absence of the parameter.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Queries and Nulls (2000)

    In an empty column, type [Enter Status] in the Field row.
    Uncheck the Display box for this column.

    Set the criteria as in the attached picture. The extra column makes it possible to distinguish between the parameter being null or not.
    Attached Images Attached Images

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access Queries and Nulls (2000)

    Thanks . That seems to be the solution.
    Shame Microsoft couldn't have built the option in to include or exclude nulls with the wild card *
    Andrew

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access Queries and Nulls (2000)

    Thanks.
    That is the solution tested it and it works well.
    Just what we needed.

    A pity a simpler solution could not have bee provided by microsoft.
    Andrew

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access Queries and Nulls (2000)

    Sorry to quibble with you, but it seems the solution from Hans is quite simple - the SQL String looks like this:
    <font color=blue>
    SELECT tbldata.status
    FROM tbldata
    WHERE (((tbldata.status)=[Enter status]) AND (([Enter status]) Is Not Null)) OR ((([Enter status]) Is Null));
    </font color=blue>
    In all candor, in 10 years of Access development your situation is the first I've seen where someone wanted to see all records when they were using a Like clause, but didn't enter any data for the parameter prompt. So we would have spent lots of time completing some sort of option to give us or not give us all records when someone left the parameter prompt empty. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Wendell

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Queries and Nulls (2000)

    Hi Andrew,

    In fact, the "Is Not Null" condition can be omitted, so the solution is even simpler. The SQL is something like

    SELECT *
    FROM tblData
    WHERE Status=[Enter Status] OR [Enter Status] Is Null;

    And although I thought of it myself, I'm not original: I just found that Microsoft has an equivalent suggestion in ACC2000: Showing All Records (Including Null) in a Parameter Query.

    Regards,
    Hans

  14. #14
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access Queries and Nulls (2000)

    Thanks Hans.
    I guess if I'd thought about it a bit longer I would have homed in on this solution.
    But then that's the great thing about forums like this.
    On days when your minds not on the job there's always someone else who can help out.
    Andrew

  15. #15
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access Queries and Nulls (2000)

    Well, in all candor, I've had this question posed to me 3 times in the last 6 months of Access development.
    In most cases there were good design reasons for handling the problem differently, but in the last case it was worth going down this route.
    Maybe it's a British thing.
    Andrew

Posting Permissions

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