Create a SELECT query based on user input

[mdsoren]

HELP!!! I have been trying find a way to create a SELECT query at runtime based on the user selecting values from five combo boxes. They must select at least one, but may select any combination or even all five. The data comes from a hierarchy of tables. My table structure can be one, two or three tables deep depending on what selection criteria they elect and may be comparing values in as many as six tables looking for a match. The SELECTed data always comes from the same two tables and will be the same fields no matter what search criteria they have elected to filter on.

Does anyone have an idea how to go about setting up this query??? I have tried using UNIONs, but the result ends up like an OR query rather than an AND showing any record that matches even one of the criteria.

I NEED HELP - PULL-EZE!

TIA,
MDS

 

[johnwm]

I've built such queries on the fly.
First build a 'WHERE' string:

strWhere = " WHERE 1 = 1 " 'this guarantees a valid Where string if nothing else is added

then go through all the textboxes (or combos or whatever, adding to the String as required:

If len(text1.Text) > 1 then strWhere = strWhere & " AND Where fldName = '" & trim(text1.text) & "'"

If Combo1.ListIndex > 1 then strWhere = strWhere & " AND Where .... etc, etc


Then finally build the query:

srSQL = "Select fldWhatever from tblWhatever " & strWhere & " Order by fldWhich;"
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[mdsoren]

Thanx all. I have tried to build the query in pieces and have even gotten it to run now, but I am not getting the results I expected from query. It returns ALL records in my "parent" table everytime, no matter what selection criteria I use.

My problem I am sure is related to my having to look at hierarchical data. I match the user's criteria against records in "child" tables (and in one criteria's case a "grandchild" table!) and want to return the sychronized records from the "parent" table.

For example - My parent table is a list of corporations, the children include an organization table with the location (US state) of the corporation, an officers table with the corporate officers, etc. The user says they want all corps where there is an officer named John Smith and the corporation resides in Iowa (All criteria is selected from combos, so I know the exact values they are searching for).

How do I search the "child" tables and return ONLY the sychronized, matching records from the "parent" tables?!

Still in desperate need of help ,
MDS