IIF Null Query 2

[Hinfer]

I have a search form. In the search form I have a first name field and a last name field. I want it to be able to search by first or last name.

If I enter only a last name (Smith)I should get results of all people with last name Smith.

If I enter only a first name (John)I should get results of all people with first name Smith.

If I enter a first name, John, AND a last name, Smith, I should get specific results matching that criteria.

I have a search button connecting to a query.

Here is my sql for the query:

SELECT tblClient.lastname, tblClient.firstname
FROM tblClient
WHERE
(((tblClient.lastname)IIf(IsNull([Forms]![frmSearch Multiple]![lastname]),"*",[Forms]![frmSearch Multiple]![lastname]))
AND ((tblClient.firstname)IIf(IsNull([Forms]![frmSearch Multiple]![firstname]),"*",[Forms]![frmSearch Multiple]![firstname])));


This will give me a results if I have entered both fields. If I only enter one and not the other(null) then it will not work.

Please help!

 

[tempclerk]

Have you tried Like *?

 

[CosmoKramer]

Also, change the AND operator to an OR to get either one....

 

[Hinfer]

Tempclerk: I am not sure if I am using the like * correctly?
Where do I place it? Do I need quotes?

CosmoKramer: Using the OR operator will not give me the correct results. If I have enter John as first name and Smith as last name it will give me all results with all John with that first name and all Smiths of that last name. I need results with John AND Smith.

 

[Hinfer]

Tempclerk: I am not sure if I am using the like * correctly?
Where do I place it? Do I need quotes?

CosmoKramer: Using the OR operator will not give me the correct results. If I have enter John as first name and Smith as last name it will give me all results with all John with that first name and all Smiths of that last name. I need results with John AND Smith.

The <= or >= if front of IIF does not work as well

 

[tlbroadbent]

Try this.

SELECT tblClient.lastname, tblClient.firstname
FROM tblClient
WHERE ((tblClient.lastname Like &quot;*&quot; & [Forms]![frmSearch Multiple]![lastname] & &quot;*&quot
OR ([Forms]![frmSearch Multiple]![lastname] Is Null))
AND ((tblClient.firstname Like &quot;*&quot; & [Forms]![frmSearch Multiple]![firstname] & &quot;*&quot
OR ([Forms]![frmSearch Multiple]![firstname] Is Null)); Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Hinfer]

tlbroadbent: THANK YOU SO MUCH!!! THIS WORKS IN ALL INSTANCES!!! I HAVE VOTED FOR YOU FOR TIPMASTER OF THE WEEK!

 

[Hinfer]

tlbraodbent:

There are some minor flaws in your code above.

The code above allows one to type &quot;t&quot; in the last name and it will return a result for Smith because the letter &quot;t&quot; is in the last name Smith. The wildcard placements allow any last name to be queried as long as it has the one or more of the right letters.


The correct code is

SELECT tblClient.lastname, tblClient.firstname
FROM tblClient
WHERE ((tblClient.lastname=[Forms]![frmSearch
Multiple]![lastname])
OR ([Forms]![frmSearch Multiple]![lastname] Is Null))
AND ((tblClient.firstname=[Forms]![frmSearch
Multiple]![firstname])
OR ([Forms]![frmSearch Multiple]![firstname] Is Null));

Nevertheless, I couldn't have figured this out with your help.

Thanks

 

[tlbroadbent]

You are correct. I should have been more careful in considering your requirement statement. The code I proposed allowed the user to enter a partial name. If you want to allow a user to enter the first few characters of a nmae such as &quot;smi&quot; you could use the LIKE predicate but only include the wildcard at the end of the string.

Glad you worked it out. Thanks for the vote. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.