Select Menu Probability

[ai3rules]

I have 4 select menu's and a text box. I will be using those for an advanced search feature on my site. So, that means that the user has the ability to choose 1, 2, 3, 4 or 5 ways of searching in any combination.

The way I have been doing it until now is programming for every scenario. For example:

IF agent <> "" AND os <> "" AND whatDay <> "" AND strSearch <> "" THEN
Recordset1.Source = "SELECT * From queryAll " _
& "WHERE Agent LIKE '%" & Replace(agent, "'", "''") & "%' "_
& "AND OS LIKE '%" & Replace(os, "'", "''") & "%' "_
& "AND Day LIKE '%" & Replace(whatDay, "'", "''") & "%' "_
& "AND LastName LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY Machines.MachineName;"
END IF




I am doing that for every possible scenario. I am wondering, is there an easier way to do this?

I appreciate any help,
Thank you.

[ray326]

I don't know what you mean by "ways of searching" but I like building a semi ad hoc query by building each piece:

Result - basically the "select" clause that defines the "shape" of the result set

From - the tables that will be used in the query

Where - the actual comparisons for the "where" clause

Order - the columns and sort direction used in the "order by" clause

The systems I do this with are written in Java so I have the power of OO to structure the final code using inheritance but the overall thought process is the same regardless. In your main line the build for your SQL becomes something like

Recordset1.Source = result() & from() & where() & order()

Then the functions either return a clause for the SQL or " " if they have nothing to do.