Help with conditional statement in query

[batowl]

Hi Everyone,

So I have a database with one table (Data Table) in it that stores all the data. I want my users to be able to search the database on a particular drop-down field in this table, let's call it Disease, so I created a separate table (Search Table) with an identical Disease field in it and created a relationship between the Disease fields in each table. The users then choose which disease they want to search the database on by choosing a disease from the drop down list in the Search table. That is all well and good.

However, there are times when my users will leave the Disease field blank in the Search Table (because they will want to return results for all diseases, not just one). That is where I am running into a problem, I cannot get the expression in the query to return the correct results. Here is the expression that I have built:
IIf([Search Table].[Disease], [Data Table].[Disease],[Search Table].[Disease)


Any suggestions on how to write this expression so that if my users choose a disease from the search table, only those records in the data table are returned that match the disease they chose OR if they choose to leave the search field blank, it returns all the records in the data table?
Thank you.

[dreday]

What you can do is eliminate the search table and try dynamically creating an SQL statement with the code.

Let's say instead of binding the search form to your original table, you bind it to an SQL statement like "Select * from [DataTable]" as your recordsorce.

Now, in your command button do something like this:

If isnull([dropdown name]) Then
Open Search Form
Else
[FormName].Recordsource = [FormName].Recordsource & " Where [Disease] = " & [dropdown name]
Open Search Form
End if

Basically if they leave the box blank you do nothing and open the form with all the records. If the box isn't blank, you append a where clause to the original SQL statement and filters the records automatically.

Hope this helps