Create a SELECT query based on user input

[mdsoren]

I have been hunting for a solution for this for two months and have yet to find it!

I have a VB application which allows the user to select one, any combination or all five variables to search on (Owner's name, Attorney's name, State, Company Type and Accounting Firm used). The report to be returned is always the same, and lists the Company Name, whether organized or authorized for business within a state (represented by an "O" or an "A&quot, the State the company is organized/authorized in, and their attorney.

I know how to write the queries to find any single criteria, but I don't understand how to write it if the user selects more than one. In code I can test to see what values they have selected and which are blank, but HOW do I assemble a SQL statement on-the-fly to limit the result set??? What I need is to create an "AND" search without knowing in advance what the "AND's" are!

And let's complicate it just a little more, the four fields in the report come from 3 different tables, and the balance of the database to be searched is comprised of another 8 or so tables in a hierarchical fashion with four levels of hierarchy!

Can someone help me!?! This is the final piece in the application which is supposed to be delivered next Monday!

 

[psemianonymous]

This is how I've done it. Each "item" is surrounded by parentheses and is followed by " AND ". Then, after my "filter/datasource assembly" code finishes, I chop off the last 5 letters of the SQL statement, removing the last " AND ", and thus completing the statement. You'll be fine--just make sure each criterion is independent of the others. So an example:

filter = ""

'Option 1: sort by date:
If (sortByDate = true) then
filter = filter & "(DATE_FIELD BETWEEN #" & etc1 & "# AND #" etc2"#)" & " AND "
end if

'Option 2: Sort by code:
If (sortByCode = true) then
filter = filter & "(CODE_NUMBER = " & etc1 & ") AND "
End If

'etc etc
filter = Left(filter, Len(filter) - 5)

 

[mdsoren]

It would seem that simple, but I don't think it is... because of the herarchy of the tables. I'll try and give an example here:

if they want to find John Brown (an owner), the query has to search the officer's table, the owner's table and the main table (where he may be listed as a contact). The officer's table is linked to the main table by an ID#, owner's table is also linked to the main table. If a match is found, I then have to pull the matching company and associated attorney from the main table and the state and whether organized or authorized out of the Organization table, which is also linked to the main table. Now imagine they also want to know if John Brown is connected to any companies, but only if they are in Iowa... !

HELP!

 

[psemianonymous]

Hmm, try and get all that info into one humongous query? Link the ((((main table LEFT JOIN to Officer) LEFT JOIN to Owner) LEFT JOIN Company) LEFT JOIN MAIN as ATTORNEY) LEFT JOIN Organization ...

It's easier in Query builder than it is in SQL. If you can manage to fit it into one query, no matter HOW SLOW, just do it.

Otherwise, good luck.