G
Guest
I'm trying to set up a search function on my form which will allow end users
the fexibility to either enter information if they would like to narrow their
search, or leave fields blank if they do not need to narrow that specific
portion of the search. The general formula I am using for the filter query is:
Like IIf([Search Field on Form] is null,"*",[Search Field on Form])
This should, as I understand it, filter selections with the information in
the search field only if information is entered, and if no information is
entered, should not filter using that field at all. For some reason however,
my search does not filter by the selections that I enter. Any help that can
be offered will be appreciated.
The following is the full SQL for my query
SELECT *
FROM [CSTS Results]
WHERE ((([CSTS Results].[Associate Director]) Like IIf([Forms]![CSTS Detail
Subform]![ADList] Is Null,"*",[Forms]![CSTS Detail Subform]![ADList])) AND
(([CSTS Results].Supervisor) Like IIf([Forms]![CSTS Detail Subform]![SupList]
Is Null,"*",[Forms]![CSTS Detail Subform]![SupList])) AND (([CSTS
Results].Representative) Like IIf([Forms]![CSTS Detail Subform]![RepList] Is
Null,"*",[Forms]![CSTS Detail Subform]![RepList])) AND (([CSTS
Results].[Contact Date]) Like IIf([Forms]![CSTS Detail Subform]![SupList] Is
Null,"*",[Forms]![CSTS Detail Subform]![SupList])) AND (([CSTS
Results].[Respondant Number]) Like IIf([Forms]![CSTS Detail
Subform]![SupList] Is Null,"*",[Forms]![CSTS Detail Subform]![SupList])));
the fexibility to either enter information if they would like to narrow their
search, or leave fields blank if they do not need to narrow that specific
portion of the search. The general formula I am using for the filter query is:
Like IIf([Search Field on Form] is null,"*",[Search Field on Form])
This should, as I understand it, filter selections with the information in
the search field only if information is entered, and if no information is
entered, should not filter using that field at all. For some reason however,
my search does not filter by the selections that I enter. Any help that can
be offered will be appreciated.
The following is the full SQL for my query
SELECT *
FROM [CSTS Results]
WHERE ((([CSTS Results].[Associate Director]) Like IIf([Forms]![CSTS Detail
Subform]![ADList] Is Null,"*",[Forms]![CSTS Detail Subform]![ADList])) AND
(([CSTS Results].Supervisor) Like IIf([Forms]![CSTS Detail Subform]![SupList]
Is Null,"*",[Forms]![CSTS Detail Subform]![SupList])) AND (([CSTS
Results].Representative) Like IIf([Forms]![CSTS Detail Subform]![RepList] Is
Null,"*",[Forms]![CSTS Detail Subform]![RepList])) AND (([CSTS
Results].[Contact Date]) Like IIf([Forms]![CSTS Detail Subform]![SupList] Is
Null,"*",[Forms]![CSTS Detail Subform]![SupList])) AND (([CSTS
Results].[Respondant Number]) Like IIf([Forms]![CSTS Detail
Subform]![SupList] Is Null,"*",[Forms]![CSTS Detail Subform]![SupList])));