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 type '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.

 

[rac2]

Define a variable that is false when something is entered into the field and true when the field is left empty.

Function anyName()
If IsNull(Form_Search.Crit1) Then
   anyName = true
Else
   anyName = false
End If
End Function

Add this variable to the WHERE clause, that is, the criteria for the search field like this.

 . . .
WHERE ( anyName = true OR colName = Forms![Search]![Crit1]
)

The criteria will be true for all records in the table when anyName is true. When anyName is false the criteria will be true only for rows where colName matches the value that was entered. In my example colName is the column in your table that is being searched.

I am not familiar with VBA syntax so my code may be wrong, but I think you will see what I mean.