Access Queries and Nulls (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

You can simplify it like this:

Like [Enter Status] & "*"

or

Like Nz([Enter Status],"*")

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 *

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Sorry to quibble with you, but it seems the solution from Hans is quite simple – the SQL String looks like this:

SELECT tbldata.status
FROM tbldata
WHERE (((tbldata.status)=[Enter status]) AND (([Enter status]) Is Not Null)) OR ((([Enter status]) Is Null));

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote