Managing And's & Or's in a SQL statement

[Iainkerr01]

I am creating a query system for my users so that they can generate a result set for themselves.
The form has 4 columns as follows.
1. the 'Join' field (ie blank, and, or)
2. the search field name
3. the condition field (ie = , <>, Like)
4. the value of the field to search on.

The form will have up to 4 rows allowing the uses to create a more complex query involving "and's" & "or's". The query may not necessarily have all 4 rows filled.

The information from each of the rows will be added together to form a SQL statement.

The 'Join' field from row 1 will always be an 'And' as this will be linked to a general 'Where' statement.

Does anyone have a module already written which will help me manage the necessary bracketing within the SQL statement particularly where 'ors' are used?

Thanks in advance.

 

[BobRodes]

First, I assume that you are talking about customizability with the WHERE clause, and are not using the term "join" in the sense that it is used in SQL.

It seems you're talking about a sort of QBE (Query by example) concept. QBE convention is that columns are in logical and and rows in logical or. If you adopt this convention rather than asking the users to enter ands and ors here and there, you should greatly simplify your problem. (As an example, this is how search criteria are put in in Excel, not to mention Lotus 1-2-3 if you go back that far.) Just put up a grid, have the users fill them in with this understanding (columns are and, rows are or) and then create your statement by reading the values in the cells.

HTH

Bob