Problem with more than TWO criteria (1 Viewer)

seabass

New member
Local time
Today, 10:22
Joined
Apr 7, 2006
Messages
6
:confused: 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! :eek: 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] & "*")

:cool:LOL :rolleyes:
 

EMP

Registered User.
Local time
Today, 10:22
Joined
May 10, 2003
Messages
574
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

New member
Local time
Today, 10:22
Joined
Apr 7, 2006
Messages
6
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 :confused: :confused:

Im gonna have some headache pills right now :(

Goodluck EMP! :) Cheers
 

Attachments

  • search.zip
    86 KB · Views: 96

EMP

Registered User.
Local time
Today, 10:22
Joined
May 10, 2003
Messages
574
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

New member
Local time
Today, 10:22
Joined
Apr 7, 2006
Messages
6
:D 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! ;) :D
 

Attachments

  • Working Multiple Criteria Search.zip
    20.8 KB · Views: 120

Users who are viewing this thread

Top Bottom