Query Criteria Crisis--this sucks

[smbarney]

I have a form called "Search" on which there are a number of unbound text field. The idea is this: the user types in information on a subject (like first name, last name, Date of Birth etc) and then hits search. The form calls a query named qMasterSearch. The search results are displayed in the FormFooter of the Search form. In the relative field criteria box of the query, I have put in: Like [Forms]![Search]![Ben_Zip] & "*" So long as the corresponding field in the form has something, it works perfect. The problem is we don't always have all of the information for each record resulting in tons of blank fields. What we need, therefore, is a way to run the query with what every information is there but if the form field is blank just display all of the records. I tried to use "*" in If null statement. However, this excludes null fields. I also tried using Form Filter in vb on the Search form with if nz() statements. However, because of the size of the database (70 million plus records--I have no control over this) it first tries to pull all of the records first before applying the filter. With the large number of records, this does not work.

Basically, I need some way to run a query and when a field is blank on the Search form the query understands that it is to display all records for that column. Can someone please help me? You have no idea how important this project is. Thanks.

 

[Brianwarnock]

Does this help

Brian

 

[smbarney]

Thanks!

Does this help

Brian

You Rule!!! Thanks. I knew someone had come across this problem and found a solution. Thanks again.

 

[smbarney]

Does this help

Brian

Grrrr....I forgot to mention that the back end is SQL(my bad). As such, when I tried your suggestion it gives me a OBDC failure error. I am not sure why. Any more thoughts? This is driving me crazy....

 

[Brianwarnock]

Sorry no experience of backend SQLs. If nobody comes in on this then post afresh with all the info, perhaps in SQL server thread.

Brian

 

[pbaldy]

I use SQL Server a lot; I only glanced at the linked thread, and didn't see anything right off that wouldn't work with SQL Server. What exactly do you have in your SQL? And what's the ODBC error?

 

[smbarney]

I use SQL Server a lot; I only glanced at the linked thread, and didn't see anything right off that wouldn't work with SQL Server. What exactly do you have in your SQL? And what's the ODBC error?

I cannot say what is in the database. However, all of the fields are text (even the dates and other numbers!!) which causes problems. The size also causes problems. If you run too broad of a search it will take it two and half years to run.

I did read in this forum somewhere that you are limited to about six statements consisting of:
Like [Forms]![Search]![Bet_Last_Name] & "*" or Like [Forms]![Search]![Last_Name] is null.

This did work, but only in a limited way. After six statements, however, it overloads the system as the SQL where statement becomes too big.

Is there a way I could put in the search, that if null, do nothing and display all records? This seems so strange that you cannot display both non-null and null fields. Thanks.

 

[pbaldy]

Actually I meant the SQL of your query, not the contents of the database itself. What I usually do if I have a number of search fields that may or may not be used is build the SQL dynamically. That way I only include those fields that were actually filled out by the user. Depending on the situation, I may either build a wherecondition for OpenReport or build a query or even a passthrough query to SQL Server. Given the number of records you have, you may want to try that last one; it would make sure all processing is done by the server. Is that something you're familiar with?

 

[smbarney]

SQL and I are only lukewarm friends at best. Not sure about a passthrough query. I thought about building the SQL query dynamically, but could not get it to work. Any help you could offer me would be appreciated more than you could know.

 

[pbaldy]

This is on another site, but it's a good tutorial on building SQL in VBA and more importantly the sample db shows one way to do it:

http://www.mdbmakers.com/forums/showthread.php?t=4895

If you want to try a pass-through query, start a new query. While it's in design view, click on Query/SQL Specific/Pass Through. That should bring you to a blank SQL view. Click on View/Properties. You want to set the ODBC connection string. Presuming you use linked tables, just use the same DSN you use for them. Try it with a simple "SELECT * FROM TableName" to make sure it works (if you use that massive table, better add a WHERE clause).

The trick to using a pass-through is that you have to use functions/SQL the server understands. For instance, SQL Server doesn't know what an IIf function is; you'd have to use a T-SQL equivalent, the CASE statement. If you get hung up, post the SQL and we'll sort it out.