Multiple Search Criteria

[jwelt]

Hi,
I'm trying to create a search engine that will take several criteria from several fields and cross refrence the results, bringing up a list of matching records. The best way to do this, I think, would be with a query, but I want the criteria to come from a form so it's easier for the less access-literate to use. I've set the criteria to be from this search form like this:
Forms![Search]![Crit1]
When the data is entered into all fields, it works fine. However, the problem is, when I search for just "Name" without "Address" (leaving the 'Address' field blank), I get no results.

I've tried to get around the problem by using a module with functions Name() and Address() in they're respective criteria boxes. Then the functions would read something like this :

Function Name()
If IsNull(Form_Search.Crit1) Then
Name = "Is Null Or Is Not Null"
Else
Name = Forms![Search]![Crit1]
End If
End Function

I think this should work, since if I put 'Is Null Or Is Not Null' in the criteria box, it works like there is nothing there, (which is what I want, if it's not one of the limiting criteria). But instead of entering Is Null Or Is Not Null in the criteria box, it's searching for the string "Is Null Or Is Not Null" in the records! How would I enter the criteria "Is Null Or Is Not Null" into a criteria box without searching for the literal string using a VB function?

Or, is there another way I should do this?

P.S. I'm just learning Access at present and I'm not that good at it, so please keep answers as clear as possible.

 

[grnzbra]

Perhaps you could open a recordset based on a sql statement such as:
sqlstr = "SELECT * FROM yourtable WHERE " & whereclause

to get whereclause, loop through the fields on the form and concatinate each field followed by the word "and" and then do a left(whereclause, Len(whereclause)-3) this last gets rid of the final "and"


By the way, have you tried the "filter by form" process?

 

[jwelt]

I'm afraid that I'm not sure what it means to 'open a recordset based on a sql statement' I don't really know how to program in sql. Is there a way to do this using simple VB? Maybe you could give an example to help? What's a whereclause? Sorry about the flurry of questions but, I'm not that good at this. Is there any way to use a VB function to accomplish the Is Null Or Is Not Null thing?

I have looked at the "filter by form" process a bit, but the problem with that is I'm eventually going to have to search through multiple tables of unrelated records. It spans several different forms. Anyway, it needs to be a list of records. My boss doesn't want to cycle through all the matches looking for the one he wants.

There wouldn't be anyway to make an 'On Click' event in a query is there? That way, once the desired record is touched on, the form to which it belongs could be brought up.