user defined selection criteria form / SQL

Hi
I wish to give Users the option of searching a database for a particular record(s) (OK, tuples as it is relational) based on the User's own search criteria. If there isn't anything out there I may write it myself and post it here.

What I want to be able to do is to provide a form with a combobox control which lists all the field (Column) names and allows the user to select from this (unlimited - well quite a few - criteria would be allowed). There are also two txtboxes next to this for providing either a single search value or range of values. A few checkboxes are also provided to select (and / or / not). A List or txtbox also shows the selection criteria as it is built (the text should also be editable). A final control would be necessary to either save / run the query.

You are certainly looking for a headache.

Well, let's take a simple case and you can enhance it to suit your needs.

In your database, you have 3 fields named 'FirstName', 'LastName' and 'Age'. You have a combo on your form to display these field names. You also have a textbox to accept the criterion from the user.

The functionality of the code is to search for the user-entered value in the selected column. The code will look like this:

Code:

---

strQueryString = "Select * from <table name> where "
strQueryString = strQueryString & Combo1.text
strQueryString = strQueryString & " = '"
strQueryString = strQueryString & Text1.Text & "'"

---

Suppose the user selects 'FirstName' in the combo and enters 'honeybee' in the textbox, the strQueryString will look like:

Select * from <table name> where FirstName = 'honeybee'\

For numeric fields, you have to put validations and conversion routines. If you decide to have the and/or functionality as well, you have added tasks on your list. If this example satisfies you, you can discuss it in more details with me either on the forum or through mail.

Hope it helps...

Thanks for the input; but I already know HOW to do it; I just had hoped that someone had already tried it because I am a just TOO lazy!!

I'll do it now and post it here.

Thanks again.

Wayne

s_prog, it's nice when someone serves you food. But sometimes you just have to do it yourself.