Queries & multiple Form inputs

[Tal]

Hi All.

I know, this topic has been chewed so much, and in the tens of pages
I've read so far are tens of solutions for this problem but still - I
can't get it to work.

The participants in this scene:
1. An unbound form with simple text fields describing people (first
name, last etc.).
2. A "people" table with the coresponding data.

The problem:
To create a query which will be executed after a "search" button
click. The query will use the criteria in the form to filter the
table, null fields will present everything, the catch resides in null
valued table fields and null valued form fields.

Example for the first criteria line of the first field in the query,
"strFirstName":

Like "*"+[Forms]![frmMain]![txtFirstName]+"*"

second criteria line ("or"):

Is Null Or Like IIf(IsNull([Forms]![frmMain]![txtFirstName]),"*",0)

this works fine on its own, but when I continued with the second
field, strLastName, it did not work properly.
I do not want to use modules, I believe this is a simple this and I'm
missing something basic here. Any help would be accepted.

Here is the SQL of the whole query:

-=-=-=-
SELECT tblPeople.strFirstName, tblPeople.strLastName,
tblPeople.strCity, tblPeople.strStreet, tblPeople.strPhone,
tblPeople.strCellPhone, tblPeople.strFax, tblPeople.strEmail,
tblPeople.strKeyWords, tblPeople.strSex, tblPeople.fltHeight,
tblPeople.strBodyStyle, tblPeople.strHairStyle,
tblPeople.blPictureExists, tblPeople.blFirm, tblPeople.blActive,
Forms!frmMain!txtFirstName AS Expr1
FROM tblPeople
WHERE (((tblPeople.strFirstName) Like
"*"+[Forms]![frmMain]![txtFirstName]+"*") AND ((tblPeople.strLastName)
Like "*"+[Forms]![frmMain]![txLastName]+"*") AND
((tblPeople.blActive)=True)) OR (((tblPeople.strFirstName) Is Null Or
(tblPeople.strFirstName) Like
IIf(IsNull([Forms]![frmMain]![txtFirstName]),"*",0)) AND
((tblPeople.strLastName) Is Null Or (tblPeople.strLastName) Like
IIf(IsNull([Forms]![frmMain]![txtLastName]),"*",0)));
-=-=-=-

whew...
waiting for the rescue

Thanks.
Tal.

 

[John Viescas]

Tal-

You can probably get it to work for two parameters, but you run the risk of
getting "Query Too Complex" if you try to add a third or fourth one - which
is why most of use build the predicate dynamically in code. Try this:

WHERE tblPeople.blActive = True AND
((tblPeople.strFirstName LIKE "*" & [Forms]![frmMain]![txtFirstName] & "*")
OR
([Forms]![frmMain]![txtFirstName] IS NULL)) AND
((tblPeople.strLastName LIKE "*" & [Forms]![frmMain]![txtLastName] & "*") OR
([Forms]![frmMain]![txtLastName] IS NULL))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi All.

I know, this topic has been chewed so much, and in the tens of pages
I've read so far are tens of solutions for this problem but still - I
can't get it to work.

The participants in this scene:
1. An unbound form with simple text fields describing people (first
name, last etc.).
2. A "people" table with the coresponding data.

The problem:
To create a query which will be executed after a "search" button
click. The query will use the criteria in the form to filter the
table, null fields will present everything, the catch resides in null
valued table fields and null valued form fields.

Example for the first criteria line of the first field in the query,
"strFirstName":

Like "*"+[Forms]![frmMain]![txtFirstName]+"*"

second criteria line ("or"):

Is Null Or Like IIf(IsNull([Forms]![frmMain]![txtFirstName]),"*",0)

this works fine on its own, but when I continued with the second
field, strLastName, it did not work properly.
I do not want to use modules, I believe this is a simple this and I'm
missing something basic here. Any help would be accepted.

Here is the SQL of the whole query:

-=-=-=-
SELECT tblPeople.strFirstName, tblPeople.strLastName,
tblPeople.strCity, tblPeople.strStreet, tblPeople.strPhone,
tblPeople.strCellPhone, tblPeople.strFax, tblPeople.strEmail,
tblPeople.strKeyWords, tblPeople.strSex, tblPeople.fltHeight,
tblPeople.strBodyStyle, tblPeople.strHairStyle,
tblPeople.blPictureExists, tblPeople.blFirm, tblPeople.blActive,
Forms!frmMain!txtFirstName AS Expr1
FROM tblPeople
WHERE (((tblPeople.strFirstName) Like
"*"+[Forms]![frmMain]![txtFirstName]+"*") AND ((tblPeople.strLastName)
Like "*"+[Forms]![frmMain]![txLastName]+"*") AND
((tblPeople.blActive)=True)) OR (((tblPeople.strFirstName) Is Null Or
(tblPeople.strFirstName) Like
IIf(IsNull([Forms]![frmMain]![txtFirstName]),"*",0)) AND
((tblPeople.strLastName) Is Null Or (tblPeople.strLastName) Like
IIf(IsNull([Forms]![frmMain]![txtLastName]),"*",0)));
-=-=-=-

whew...
waiting for the rescue

Thanks.
Tal.