Help on query criteria for a search

[urbanhawk]

I am trying to make a multi-field search where if a user enters data into a search prompt it will return all similar entries to it and if they leave the fields in the prompt blank it will return all entries for that field including blank entries in the records. The data is stored in a table called BookTable and I have a second table called Priorsearch which stores the text the user inputted in their search. I've tried this code for the criteria:
IIf(IsNull(DLookUp("Customer","PriorSearch")),Like "*" Or Is Null,Like "*" & DLookUp("Customer","PriorSearch") & "*")
On their own each part of the codes works fine however together they won't work saying that it is giving the wrong datatype. Removing the "is null" stops that error however then it doesn't give any results at all regardless if you enter data into the field or not. The program also keeps trying to throw ([BookTable].[Customer]) in front of the "Likes". Anyone have any suggestions on how to get this to work?

[June7]

Don't understand this expression.

The DLookup will just return value from first record in PriorSearch table.

What do you mean by 'multi-field'?

Options:

1. parameterized query that refers to controls on form for inputs

2. VBA code that construct criteria string and sets form Filter property

[urbanhawk]

I want the dlookup to return a value from the priorsearch table to serve as the criteria. I set it up so that the fields on my search form are bound to the priorsearch table as its source so when a user changes a field on the search form it is updated to the priorsearch table. Then when the user hits the button to go the next form, the next form calls on this query as it's record source and in turn this query searches for the data that was stored in priorseach to use as it's criteria. This way the query is indirectly controlled by the controls on the search form and stores the data so it lets you back peddle through the system later on and have the program still be able to call up your previous search.

As for multi-field I mean I am going to have multiple textboxes where a user can input different fields of information into the system. I want to set it up so that in one field a user could search soley for customer, and in a different field they can search by machine types, or if they want to they can search by both customer and the part number, ect...

[June7]

Sorry, still not understanding the DLookup.

Regardless, do you want option 1 or option 2?