Wildcard Filter Issues

J

Jahlu

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])));
 
J

Jahlu

Ok,

Here is an update, I have been able to determine that the query itself is
working, what appears to be happening (and is probably much more rudimentary)
is that I will enter information into a field and then try to filter before
tabbing out of the field, and that is where I end up having problems. I am
still somewhat new to Access, does anyone know how to correct this so that my
end users do not have to tab out of the last field before clicking on the
search button?
 
D

Douglas J. Steele

Just a comment.

Like Nz([Forms]![CSTS Detail > Subform]![ADList],"*")

might make your query a little easier to read.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jahlu said:
Final update: Nevermind, a simple requery was all that I needed... thanks
anyway!

Jahlu said:
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])));
Click to expand...
Click to expand...
 
A

Allen Browne

Jahlu, please be aware that the criteria:
Like IIf([Search Field on Form] is null,"*",[Search Field on Form])
does *not* return all records when the parameter is left blank.

The records that are Null (i.e. where nothing has been entered into the
field) are excluded by that criterion. Null doesn't match anything, and so
Like "*" actually excludes the Nulls.

You can re-design the WHERE clause of the query like this:

WHERE (([Forms]![CSTS Detail Subform]![ADList] Is Null)
OR ([CSTS Results].[Associate Director]) = [Forms]![CSTS Detail
Subform]![ADList])) AND ...

But that still gets very inefficient when you have lots of criteria. A
better solution is to build the WHERE clause dynamically, using only the
boxes where the user actually entered something. You can then use it as the
Filter for a form, or the WhereCondition for OpenReport.

Here's an example of how to code that:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The sample database shows how to do that with different field types, exact
and partial matches, and ranges of values (e.g. a month).

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

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

Jahlu said:
Final update: Nevermind, a simple requery was all that I needed... thanks
anyway!

Jahlu said:
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])));
Click to expand...
Click to expand...
 
J

Jahlu

Thanks, I had to removed the ">" from your suggestion (minor issue) but this
does help to clean up my code and make it easier to read

Douglas J. Steele said:
Just a comment.

Like Nz([Forms]![CSTS Detail > Subform]![ADList],"*")

might make your query a little easier to read.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jahlu said:
Final update: Nevermind, a simple requery was all that I needed... thanks
anyway!

Jahlu said:
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])));
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry. The perils of copy-and-paste! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jahlu said:
Thanks, I had to removed the ">" from your suggestion (minor issue) but
this
does help to clean up my code and make it easier to read

Douglas J. Steele said:
Just a comment.

Like Nz([Forms]![CSTS Detail > Subform]![ADList],"*")

might make your query a little easier to read.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jahlu said:
Final update: Nevermind, a simple requery was all that I needed...
thanks
anyway!

:

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])));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Validation Rules for a Control on a Form (?) 4
iif exists - too complex? 0
Check box to control editablility of text box and return all recor 1
main form totals fail when subform total is null 5
Go to a Specific Record on a Subform 3
Filter a list containing null values 3
force detail on form to show 0
Set focus in a specific record within a subform 1

Top