Re: QUERY - AND/OR for search panel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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!


"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BlueWolverine" <BlueWolverine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C76A5734-A782-46A9-8A47-1086FD8746B3@xxxxxxxxxxxxxxxx
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
MSE - Mech. Eng.
Go BLUE!


.

Relevant Pages

  • Re: Can I do this Query?
    ... In SQL, this is: ... select orderNo ... You now try to fill as many boxes as possible ... ... I need to create a query that returns the number of boxes required per customer order. ...
    (microsoft.public.sqlserver.server)
  • Re: New User- Need help with Data Entry
    ... combo boxes. ... If you created the form from fields of multiple tables, then Access will have created a query to be the form's recordsource, and not all queries are updatable. ... If it's the name of a stored query, please post the SQL View of that query. ...
    (microsoft.public.access.forms)
  • Re: Box query
    ... from boxes b1, boxes b2 ... The query is a set intersection join kind of query which is ... expressed in SQL is either query with 2 levels nesting subqueries, ... subquery in the having clause. ...
    (comp.databases.theory)
  • Re: Check box to control editablility of text box and return all recor
    ... It is possible to craft the WHERE clause of the query so that it returns a TRUE expression if the controls are null, ... However, you may find that it is better to omit the WHERE clause from the query, and create a filter string in code. ... boxes and, and three combo boxes, ...
    (microsoft.public.access.forms)
  • Re: Using combo box selection to supply criteria for query
    ... Sorry, I am still learning SQL and VBA, I don't think that I made the ... The form contains 5 combo boxes; SBT Account, Region, ADM, Month, Year ... If I only make a selection in the Region combo box ... Post the query SQL. ...
    (microsoft.public.access.forms)