display all when NULL

[Mr.K]

I have a query which based on some fields on a form should display a set of records. In case when the user doesn't specify anything in the combobox on the form (value=NULL) I want the query to display all the records (like "*"). For some reason when i put the following in the criteria of the query it returns 0 records instead of displaying all:

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

I tried: IIf(IsNull([Forms]![frmClassReport].[ClassName]),"class_name",[Forms]![frmClassReport].[ClassName])
... and that works properly. I tried several scenarios and pretty much narrowed the problem down to the use of "LIKE" which when used as specified in the first SQL statement doesn't return any records. What am I doing wrong?

 

[Mr.K]

solution

Found this by "accident" while looking for something else:

Yeah, I figured this out some time ago that wildcards don't work properly when you try to do this. You have to use the originating field name.

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

Assuming that "Software" is the field you are putting this IIF statement in the Query. It is basically saying if you can't find anything in the Search field on the form, then each record must match itself.