QUERY - AND/OR for search panel

[BlueWolverine]

Hello,
I am having some problems with my search panel in Access 03 on XP Pro.

I have five fields to allow the user to search for any number (up to )
different things that might be in the field "Work Element Description".

I have a check box for wether they use AND or OR logic for the combination
of the five boxes.

My problem is how to handle Nulls. Most of the time they won't use five
fields, so 3-4 of them will usually be null. How do I Handle those?

OR logic is easy, it's five lines under the WED in design mode. AND is a
bigger problem. Here is the SQL.

SELECT q_search_exact.DayOfWeek, q_search_exact.Fleet_Activity,
q_search_exact.BinstoOther, q_search_exact.[CCC's], q_search_exact.VFG,
q_search_exact.VRT, q_search_exact.[GQRS CAT], q_search_exact.ACTION,
q_search_exact.[Work Element Description], q_search_exact.Frequency,
q_search_exact.[WES#], q_search_exact.Source, q_search_exact.ID,
q_search_exact.[Work Element Description], q_search_exact.[Work Element
Description], q_search_exact.[Work Element Description]
FROM q_search_exact
WHERE (((q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED1] & "*" And (q_search_exact.[Work Element
Description]) Like "*" & [Forms]![f_SearchPanel]![WED2] & "*" And
(q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED3] & "*" And (q_search_exact.[Work Element
Description]) Like "*" & [Forms]![f_SearchPanel]![WED4] & "*" And
(q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED5] & "*"));


It works for 4 nulls and a valid search, but when I replace a null with a
distinguishing second seach term, it returns the results of the first search.

Any ideas?

 

[Allen Browne]

From your example, I assume that:
a) the AND is intended to find a record where the [Work Element Description]
contains ALL the words the user entered in the one record
b) that the entire operation is an AND or an OR (i.e. you are not trying to
mix ANDs and ORs.)

So the issue is how to ignore the null search boxes?

Each phrase of the WHERE clause will need to be crafted to return TRUE if
the search box is left Null. It will look like this:

WHERE (([Forms]![f_SearchPanel]![WED1] Is Null)
OR (q_search_exact.[Work Element Description]
Like "*" & [Forms]![f_SearchPanel]![WED1] & "*"))
AND (([Forms]![f_SearchPanel]![WED2] Is Null)
OR (...

An easier (and more efficient) solution would be to build the WHERE clause
dynamically from only those boxes where the user actually entered something.
You can then use it as the Filter of a form, the WhereCondition of
OpenReport, or to stick into a full SQL statement as the RecordSource of a
form/report or the SQL property of a QueryDef.

For a downloadable example of how to build the WHERE clause dynamically,
see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

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

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

Hello,
I am having some problems with my search panel in Access 03 on XP Pro.

I have five fields to allow the user to search for any number (up to )
different things that might be in the field "Work Element Description".

I have a check box for wether they use AND or OR logic for the combination
of the five boxes.

My problem is how to handle Nulls. Most of the time they won't use five
fields, so 3-4 of them will usually be null. How do I Handle those?

OR logic is easy, it's five lines under the WED in design mode. AND is a
bigger problem. Here is the SQL.

SELECT q_search_exact.DayOfWeek, q_search_exact.Fleet_Activity,
q_search_exact.BinstoOther, q_search_exact.[CCC's], q_search_exact.VFG,
q_search_exact.VRT, q_search_exact.[GQRS CAT], q_search_exact.ACTION,
q_search_exact.[Work Element Description], q_search_exact.Frequency,
q_search_exact.[WES#], q_search_exact.Source, q_search_exact.ID,
q_search_exact.[Work Element Description], q_search_exact.[Work Element
Description], q_search_exact.[Work Element Description]
FROM q_search_exact
WHERE (((q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED1] & "*" And (q_search_exact.[Work Element
Description]) Like "*" & [Forms]![f_SearchPanel]![WED2] & "*" And
(q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED3] & "*" And (q_search_exact.[Work Element
Description]) Like "*" & [Forms]![f_SearchPanel]![WED4] & "*" And
(q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED5] & "*"));


It works for 4 nulls and a valid search, but when I replace a null with a
distinguishing second seach term, it returns the results of the first
search.

Any ideas?

 

[BlueWolverine]

Here is the SQL that got the job done.

For an OR Query between five boxes:

SELECT q_search_exact.DayOfWeek, q_search_exact.Fleet_Activity,
q_search_exact.BinstoOther, q_search_exact.[CCC's], q_search_exact.VFG,
q_search_exact.VRT, q_search_exact.[GQRS CAT], q_search_exact.ACTION,
q_search_exact.[Work Element Description], q_search_exact.Frequency,
q_search_exact.[WES#], q_search_exact.Source, q_search_exact.ID
FROM q_search_exact
WHERE ((((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED1 & "*" And Forms!f_SearchPanel!WED1 Is Not Null) Or
((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED2 & "*" And Forms!f_SearchPanel!WED2 Is Not Null) Or
((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED3 & "*" And Forms!f_SearchPanel!WED3 Is Not Null) Or
((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED4 & "*" And Forms!f_SearchPanel!WED4 Is Not Null) Or
((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED5 & "*" And Forms!f_SearchPanel!WED5 Is Not Null)));


FOR an AND Query between five boxes:

SELECT q_search_exact.DayOfWeek, q_search_exact.Fleet_Activity,
q_search_exact.BinstoOther, q_search_exact.[CCC's], q_search_exact.VFG,
q_search_exact.VRT, q_search_exact.[GQRS CAT], q_search_exact.ACTION,
q_search_exact.[Work Element Description], q_search_exact.Frequency,
q_search_exact.[WES#], q_search_exact.Source, q_search_exact.ID,
q_search_exact.[Work Element Description], q_search_exact.[Work Element
Description], q_search_exact.[Work Element Description]
FROM q_search_exact
WHERE ((((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED1 & "*" Or Forms!f_SearchPanel!WED1 Is Null) And
((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED2 & "*" Or Forms!f_SearchPanel!WED2 Is Null) And
((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED3 & "*" Or Forms!f_SearchPanel!WED3 Is Null) And
((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED4 & "*" Or Forms!f_SearchPanel!WED4 Is Null) And
((q_search_exact.[Work Element Description]) Like "*" &
Forms!f_SearchPanel!WED5 & "*" Or Forms!f_SearchPanel!WED5 Is Null)));


I'm not saying it's efficient or the best method, but it worked pretty well.
granted I am not working with tons of data but I found this did work and was
easy to implement, if not to think up.

Thanks for thehelp.



--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

From your example, I assume that:
a) the AND is intended to find a record where the [Work Element Description]
contains ALL the words the user entered in the one record
b) that the entire operation is an AND or an OR (i.e. you are not trying to
mix ANDs and ORs.)

So the issue is how to ignore the null search boxes?

Each phrase of the WHERE clause will need to be crafted to return TRUE if
the search box is left Null. It will look like this:

WHERE (([Forms]![f_SearchPanel]![WED1] Is Null)
OR (q_search_exact.[Work Element Description]
Like "*" & [Forms]![f_SearchPanel]![WED1] & "*"))
AND (([Forms]![f_SearchPanel]![WED2] Is Null)
OR (...

An easier (and more efficient) solution would be to build the WHERE clause
dynamically from only those boxes where the user actually entered something.
You can then use it as the Filter of a form, the WhereCondition of
OpenReport, or to stick into a full SQL statement as the RecordSource of a
form/report or the SQL property of a QueryDef.

For a downloadable example of how to build the WHERE clause dynamically,
see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

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

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

Hello,
I am having some problems with my search panel in Access 03 on XP Pro.

I have five fields to allow the user to search for any number (up to )
different things that might be in the field "Work Element Description".

I have a check box for wether they use AND or OR logic for the combination
of the five boxes.

My problem is how to handle Nulls. Most of the time they won't use five
fields, so 3-4 of them will usually be null. How do I Handle those?

OR logic is easy, it's five lines under the WED in design mode. AND is a
bigger problem. Here is the SQL.

SELECT q_search_exact.DayOfWeek, q_search_exact.Fleet_Activity,
q_search_exact.BinstoOther, q_search_exact.[CCC's], q_search_exact.VFG,
q_search_exact.VRT, q_search_exact.[GQRS CAT], q_search_exact.ACTION,
q_search_exact.[Work Element Description], q_search_exact.Frequency,
q_search_exact.[WES#], q_search_exact.Source, q_search_exact.ID,
q_search_exact.[Work Element Description], q_search_exact.[Work Element
Description], q_search_exact.[Work Element Description]
FROM q_search_exact
WHERE (((q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED1] & "*" And (q_search_exact.[Work Element
Description]) Like "*" & [Forms]![f_SearchPanel]![WED2] & "*" And
(q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED3] & "*" And (q_search_exact.[Work Element
Description]) Like "*" & [Forms]![f_SearchPanel]![WED4] & "*" And
(q_search_exact.[Work Element Description]) Like "*" &
[Forms]![f_SearchPanel]![WED5] & "*"));


It works for 4 nulls and a valid search, but when I replace a null with a
distinguishing second seach term, it returns the results of the first
search.

Any ideas?