Problem with more than TWO criteria

[seabass]

Well here i am, puzzled again!

I have a search form with multiple criterias ...
e.g. firstname - text box, - lastname - text box, postcode - textbox. and so on.

When i have any two criteria in the criteria, it works beautifully.

IIf(IsNull([Forms]![frmSearch]![FirstName]),True,[FirstName] Like "*" & [Forms]![frmSearch]![FirstName] & "*")

IIf(IsNull([Forms]![frmSearch]![LastName]),True,[LastName] Like "*" & [Forms]![frmSearch]![LastName] & "*")


BUT! If i add a third (so now have; firstname, lastname, postcode) or more criteria, the query will not find the record.
AND I PROMISE THERE IS NO ERROR IN THE REFERENCE TO TEXTBOXES ETC

IIf(IsNull([Forms]![frmSearch]![PostalCode]),True,[PostalCode] Like "*" & [Forms]![frmSearch]![PostalCode] & "*")

LOL

 

[EMP]

If it works for two criteria, it should also work for three and more criteria.

Can you post a database (preferably in Access 2000 format) with the query, the form, and the table with some test data for us to have a look at it?

^

 

[seabass]

The requested example search database

Hi EMP

This is simply bizzarre! I can't see the woods for the trees with this problem.

I have 3 criteria that lookup for search in the customers query. These are;

IIf(IsNull([Forms]![frmSearch]![FirstName]),True,[FirstName] Like "*" & [Forms]![frmSearch]![FirstName] & "*")

Show = uncheck
Criteria = True

IIf(IsNull([Forms]![frmSearch]![LastName]),True,[LastName] Like "*" & [Forms]![frmSearch]![LastName] & "*")

Show = uncheck
Criteria = True

IIf(IsNull([Forms]![frmSearch]![PostalCode]),True,[PostalCode] Like "*" & [Forms]![frmSearch]![PostalCode] & "*")

Show = uncheck
Criteria = True

The silly thing is, is that it works with the button on the frmCustomers which does exactly the same thing as the frmSearch

Im gonna have some headache pills right now

Goodluck EMP! Cheers

 

[EMP]

There is nothing wrong with the query.

The problem lies in the GotFocus code of CustomerID.
The text boxes are set to "" instead of Null by the code. Hence the IsNull([Forms]![frmSearch]![FirstName]) etc in the query can never be true.

To set the text boxes to Null, you should use:

DoorNumber = Null
FirstName = Null
LastName = Null
Address = Null
PostalCode = Null
​

^

 

[seabass]

That was not the most obvious error "" not being null therefore never true. Thank you ever so much for giving me those pointers EMP

Here, i have posted the finished article for those who need the same answer.

I don't know of any other forum which now has working multiple criteria search with wildcard expressions and without getting real into hardcoding SQL

Great job!