Using IIF statement in Criteria

[rb608]

I want to filter records in a simple query, but based on the contents on a
text box in a form. However, I want to use the criteria only if the text
box is not null. The query works fine when there is data in the text box;
but I'm having trouble returning all of the records when the text box is
blank.

I thought this would work, but it doesn't:

IIF(IsNull([Forms]![DataForm]![txtLevel]) = True,
[Forms]![DataForm]![txtLevel], Like "*")

In place of the Like "*" part (or the entire statement) what can I use to
have the query return all records in the case of a null value text box?

TIA,
Joe

 

[Allen Browne]

Switch the query to SQL View (View menu), and set up the WHERE clause like
this:
WHERE (([Forms]![DataForm]![txtLevel] Is Null)
OR ([MyField] Like "*" & [Forms]![DataForm]![txtLevel] & "*"))

For a more efficient approach - especially if you have lots of fields to
filter like this - see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Do not declare the parameter: there's a bug in Access such that it does not
recogised declared parameters of type Text as Null. Details:
http://allenbrowne.com/bug-13.html

 

[Guest]

Allen,

Thanks for the post about the text type parameter query bug.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

Switch the query to SQL View (View menu), and set up the WHERE clause like
this:
WHERE (([Forms]![DataForm]![txtLevel] Is Null)
OR ([MyField] Like "*" & [Forms]![DataForm]![txtLevel] & "*"))

For a more efficient approach - especially if you have lots of fields to
filter like this - see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Do not declare the parameter: there's a bug in Access such that it does not
recogised declared parameters of type Text as Null. Details:
http://allenbrowne.com/bug-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to filter records in a simple query, but based on the contents on a
text box in a form. However, I want to use the criteria only if the text
box is not null. The query works fine when there is data in the text box;
but I'm having trouble returning all of the records when the text box is
blank.

I thought this would work, but it doesn't:

IIF(IsNull([Forms]![DataForm]![txtLevel]) = True,
[Forms]![DataForm]![txtLevel], Like "*")

In place of the Like "*" part (or the entire statement) what can I use to
have the query return all records in the case of a null value text box?

TIA,
Joe

 

[Guest]

Another way to handle this is to not set this parameter in the query at all.
Setup your query without the WHERE clause.

Then, in the AfterUpdate event of your search box (or in the click event of
a command button), set the forms Filter and FilterOn Property. Something
like:

Private sub txt_Filter_AfterUpdate

'this tests for Null and zero length string at the same time
If len(me.txt_Filter & "&") = 0 then
me.FilterOn = False
else
me.Filter = "[yourField] Like '*" & me.txt_Filter & "*'"
me.filterOn = true
endif

End sub

HTH
Dale