IIF and Like "*" problem

[Mr.K]

I posted a similar question but maybe complicated it too much and therefore got little help on it

Basically the following IIF in the criteria in my query doesn't return all records when value is NULL; can someone tell me why?:

IIf(IsNull([Forms]![frmClassReport].[ClassName]),(([tblClassesOffered].[Course]) Like "*"),[Forms]![frmClassReport].[ClassName])

PS. I tried just: Like "*" to make sure the query doesn't have some other reason for not returnig all records, but when criteria was just Like "*" it did return all.

 

[pbaldy]

I prefer this method:

http://www.mvps.org/access/queries/qry0001.htm

 

[Mr.K]

Thanks. It looks nicer and doesn't use IIF like the solution I just ran into, which was:

IIf([Forms]![frmReports]![Software] is not null, [Forms]![frmReports]![Software], [Software])

and I'd like to use what you pointed me to:
[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null

but I don't understand how it works, could you explain it? Also when I entered that in the criteria, upon closing the design view, the query removes(?) the second part (OR [Forms]![myForm]![myControl] Is Null). However, even though only [Forms]![myForm]![myControl] is in the criteria now when I'm in the design view of the query - it all works well

 

[Mr.K]

Oh, I see what happened: the query in the design view ads at the end:
[Forms]![myForm]![myControl]
and under criteria OR: Is Null

... so it didn't disappear but I still don't understand the syntax when the control becomes the field name in the query. Could someone translate it into plain english, please?

 

[Mr.K]

This is driving me crazy

So the method:
[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null
works well with one field.

But when I have two controls:

[Forms]![myForm]![myControl_1] OR [Forms]![myForm]![myControl_1] Is Null
and
[Forms]![myForm]![myControl_2] OR [Forms]![myForm]![myControl_2] Is Null

then the query returns no results when I leave one of the controls as NULL. (If I specify one, but leave the other one as NULL it works OK)

please help

PS. It's hard for me to troubleshoot this since as I specified in the post above I don't quite understand how this syntax works.

 

[pbaldy]

As you point out, Access will usually rebuild that to add a field for the form reference and "Is Null" as a criteria. In SQL view, it would look like:

WHERE FieldName=FormReference OR FormReference Is Null;

In any criteria, you have something that evaluates to either True or False. In other words,

Software = "Access"

will either be true or false for any given record. If that's your criteria, any record where that evaluates to true will be returned.

If the form control is left blank, "FormReference Is Null" will evaluate to true, and since it's not specific to a record, ALL records will be returned.

 

[pbaldy]

Looks like we were posting at the same time. See if my "clear as mud" explanation helped you solve the problem. Hint: proper parentheses required.

 

[Mr.K]

As you point out, Access will usually rebuild that to add a field for the form reference and "Is Null" as a criteria. In SQL view, it would look like:

WHERE FieldName=FormReference OR FormReference Is Null;

In any criteria, you have something that evaluates to either True or False. In other words,

Software = "Access"

will either be true or false for any given record. If that's your criteria, any record where that evaluates to true will be returned.

If the form control is left blank, "FormReference Is Null" will evaluate to true, and since it's not specific to a record, ALL records will be returned.

Thanks. This makes more sense now. However, I'm still stucked on the problem: when having that criteria for more then one field in the query, the query doesn't work as I want it to, meaning:

I have 2 controls on the form. Both should filter the query or not when NULL. When I leave control1 as NULL and leave 2nd one NULL then all records get displayed - which is intended. However, when I specify control1 NULL and specify control2 (other then NULL) then I still get all records. And when I specify both, query filters records on both - intended.

(I'd like to use this solution vs the one with the IIF, but can't make it work - any help? thanks in advance)

 

[pbaldy]

The hint about parentheses didn't help? Post your WHERE clause. It may help to strip out all the unnecessary parentheses that Access likes to add, and then maybe you'll see where they're actually needed.

 

[Mr.K]

Thanks Paul. I actually have more then two controls as the criteria for the query and thanks to your hint I approached the problem in SQL view which made it much, much more comprehensible. Everything works well now and it looks clean (understandable to me) with all the controls filtering properly.
Now, I have one more question. There were two controls (DateFrom and DateTo) which I had working correctly (filtering the query) but I wrote them with the use of IIF and BETWEEN. I can leave them like that but then access makes the SQL statement very long with those and incomprehensible looking, once more. Is there a cleaner way of accomplishing the same as this below, using your solution vs. the IFF?:

field: ClassBegDate
criteria: Between IIf(IsNull([Forms]![frmClassReport].[DateFrom]),#1/1/1900#,[Forms]![frmClassReport].[DateFrom]) And IIf(IsNull([Forms]![frmClassReport].[DateTo]),#1/1/3000#,[Forms]![frmClassReport].[DateTo]) Or Is Null

BTW. I also "faked" the dates as 1900 and 3000 instead of preferable "any date" which was my intention but I couldn't make it to work with the IIF.

 

[Mr.K]

...

BTW. I also "faked" the dates as 1900 and 3000 instead of preferable "any date" which was my intention but I couldn't make it to work with the IIF.

solution to the quote: http://www.access-programmers.co.uk/forums/showthread.php?t=112969