Handling nz() in an IIF statement?

[ChrisJF]

Hi there, this problem is driving me crazy!
Background:
I have a combo box which is bound to one field in a table. It is used to select a value as an input for criteria of a query.

When the combo box on the form was blank, I used the following criteria in the query to produce all results including the situation when the ChapterNo contained null.

(like "*" & [Forms]![AdminSearch]![frmAdmChapterNo] & "*&quot or (nz([Forms]![AdminSearch]![frmAdmChapterNo],"&quot="&quot

I was requested to display "ALL" as a default on the combo boxes in the form.

In the row source I have used a UNION query to add "ALL" to the list in the combo box.

SELECT DISTINCT "ALL" AS [Chapter No] from [NEM Obligations & Procedures] UNION ALL SELECT [NEM Obligations & Procedures].[Chapter No] FROM [NEM Obligations & Procedures] GROUP BY [NEM Obligations & Procedures].[Chapter No] ;

This works well!

Problem comes when you change the criteria in the query.
I am using an IIF()

like IIf([Forms]![AdminSearch]![frmAdmChapterNo]="ALL","*",([Forms]![AdminSearch]![frmAdmChapterNo])) & "*"

Now this returns all records apart from records which contain nulls, i.e records where ChapterNo is null,

It appears that the "*" passed in the query does not return any rows with null Chapter No. This was handled previously using (nz([Forms]![AdminSearch]![frmAdmChapterNo],"&quot="&quot

..and replacing "*" in the IIF statement with (nz([Forms]![AdminSearch]![frmAdmChapterNo],"&quot="&quot produces a zero result.

Hope this makes sense - any suggestions?
Regards
ChrisJF

 

[Norris68]

Try enclosing the asterisk within the bracket.

like IIf([Forms]![AdminSearch]![frmAdmChapterNo]="ALL","*",[Forms]![AdminSearch]![frmAdmChapterNo] & "*&quot

How you had it, it would append the asterisk regardless of the IIF result, so searching for 'ALL' would actually search for '**'