Using a parameter to return all records if it's left null

Hi,

I have a form which prompts the user for information and runs a query based on this information. Sometimes the user will be leaving some of the fields on the form blank but I still want my query to return all the records so I have the following in the criteria field of my query


The idea is that if the user leaves the Srch_CuttingStatus field blank the query will still return all records. However it'll only return records with something in that field and will not return records with fields that contain null values i.e. it doesn't return all records.

Using the following solution doesn't work either I'm afraid because if the user does specify criteria this will return all the records that contain null vals also which is just as undesirable.


Can someone help me out please!
Thanks,
Liam

See the following articles on Martin's Green's Access tips site: Using Parameter Queries and Parameter Queries - What If the User Ignores the Prompt?. This has also been covered here in the forums several times. Try doing a search, if you haven't already.

Like "*" excludes all null values. What you need in the IIF expression is the word True, or a condition that can be evaluated to True.

Try this in a column in the query grid (replacing with the correct field name):-

Field: IIf(IsNull([Forms]![frmViewSearch]![Srch_CuttingStatus]), True, [FieldName] = [Forms]![frmViewSearch]![Srch_CuttingStatus])

Show: uncheck

Criteria: <>False


Note  The <>False in the Criteria: cell is one of Access' query grid defaults. It tells Access to treat the IIF expression in the Field: cell as a criterion. If you directly type the IIF criterion in the Where Clause in query SQL View, you don't need to type <>False.

dcx693,

Thanks for those links. I thought I found what I was looking for with the second one. Below is taken directly from the site



"A parameter that can accept an input from the user, but that will return all records if no input is made is written..."

[type prompt here] Or Like [repeat prompt here] Is Null



This doesn't work for me. Is it me!? When I leave it blank I get all records returned but if I enter criteria it will return the matching records and all records which contain null values as well!

Jon K,

I'm a little unclear about the instructions you gave me so here's what I did. The field name in the query is Cutting Status. I left the criteria cell associated with this blank and in a new column I put in the code you suggested replacing [FieldName] with [Cutting Status]

This returns all the records all the time, that is of course if I interpreted you msg correctly.

Lads, thanks for your responses. I have searched the forum and this topic crops up all over the place however I still can't find a solution that works! I think it's time to walk away from the machine for a while...

Liam

I have attached a sample database. You can open the form, enter a Cutting Status or just leave the text box blank, and click on the command button to view the records.

The database was saved from Access 2000.

Jon k,

I have used your example as a basis for my own and I now have it working. Thanks for going to that much trouble.

Thanks again,
Liam

Jon K-

I have been using Martin Green's method. It works when the field doesn't contain null values.

Why doesn't it work when the field contains null?

Thanks.

Gib

Liam,
You're welcome. Glad that you found the example helpful.


Gib,

I have been using Martin Green's method. It works when the field doesn't contain null values.

Why doesn't it work when the field contains null?

Click to expand...

When Null operates with anything, the result is always Null. Because of this nature of Null, when you follow Martin's method and put in the criterion:-
[Enter parameter] OR LIKE [Enter parameter] IS NULL

for a field in the query grid, when the field is Null, the second part of the criterion becomes:-
OR Null LIKE [Enter parameter] IS NULL


Since Null LIKE ANYTHING returns Null, the condition is equivalent to
OR Null IS NULL, which is always True.

When the condition evaluates to True, the record is returned. Hence every Null value in the field will be returned even when the parameter is not left blank.

I hope I have explained it clear enough.


Besides, Martin's method has a side effect. When the query is saved, Access puts the criterion into two columns. If the criteria of the query involve more than one field, it will be very hard for you to correctly add the second or the third criteria for the other fields after the query is saved.

And if you switch to query SQL View, you will find that Access has added a lot of ANDs and ORs and brackets in the Where Clause (which is the query criteria), making it very difficult for you to edit the criteria there either.

It is also impossible to put in the correct criteria using Martin's
[XXXXXXXX] OR LIKE [XXXXXXXX] IS NULL  if the criteria reference controls on a form that do not contain Nulls e.g. check boxes, as in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?threadid=59793


However, with an IIF expression, we can easily tell Access what we want it to return so long as the IIF expression we put in is syntactically correct. And it remains intact after the query is saved.

Jon K

Thank you very much, Jon K

To verify what you said, I downloaded your database and created a query with this expression:-
Expr1: [Cutting Status] Like [Forms]![frmViewSearch]![Srch_CuttingStatus] Is Null

For the two records that contained Null values in the [Cutting Status] field, Expr1 always returned -1 (indicating True) even when I entered something in the text box.

Your statement: "When the condition evaluates to True, the record is returned." dawned on me that this is exactly what query criteria are all about. For instance, to return those records that contain "AA" in [Cutting Status], where [Cutting Status]="AA" must be True.

Now I understand why Martin Green's method doesn't work if the field contains Null values. I also understand why the word True in your IIF function works.

Thanks again!

Gib

I realize this is an old post but I am needing a little clarification if anyone is still connected.

I implemented Jon K's strategy for returning all records if the parameter prompt is left blank. However, what is the syntax for a situation where you have a Between [startdate] and [end date].

I attempted the following and it is apparently not working.

Thanks

IIf(IsNull([Forms]![ffilternotes]![startdate]),True,[forms]![ffilternotes]![startdate]=[Forms]![ffilternotes]![startdate]) And IIf(IsNull([Forms]![ffilternotes]![enddate]),True,[Forms]![ffilternotes]![enddate]=[Forms]![ffilternotes]![enddate])

Figured it Out

[inspdate] Between [Forms]![ffilternotes]![startdate] And [Forms]![ffilternotes]![enddate] Or [Forms]![ffilternotes]![startdate] Is Null

Criteria: <>False