Customer Search Query and from

[Simon]

I have a query that is base a form on than lets me type in some one
surname ( Or firstname or Email address) and it will do the do a
search and dispaly any one with a surname similar

the query is


SELECT qryCustomers.CustomerNumber, qryCustomers.FirstName,
qryCustomers.LastName, qryCustomers.EmailAddress
FROM qryCustomers
WHERE (((qryCustomers.FirstName) Like "*" & [forms]!
[frmCustomersSearch]![FirstNameSearch] & "*") AND
((qryCustomers.LastName) Like "*" & [forms]![frmCustomersSearch]!
[SurnameSearch] & "*") AND ((qryCustomers.EmailAddress) Like "*" &
[forms]![frmCustomersSearch]![EmailSearch] & "*"));


The problem i have if is do a search for SMITH and smith does not
have
a email addrees or first name it does not bring up any information


how do i get to serach for smith even if they do not have an emaila
address


thanks


Simon

 

[Tom Wickerath]

Hi Simon,

Try adding "OR IS NULL" to the criteria for your first name and e-mail
address fields. Something like this (untested):

SELECT qryCustomers.CustomerNumber, qryCustomers.FirstName,
qryCustomers.LastName, qryCustomers.EmailAddress
FROM qryCustomers
WHERE (((qryCustomers.FirstName) Like "*" & [forms]!
[frmCustomersSearch]![FirstNameSearch] & "*"
OR IS NULL) AND
((qryCustomers.LastName) Like "*" & [forms]![frmCustomersSearch]!
[SurnameSearch] & "*") AND ((qryCustomers.EmailAddress) Like "*" &
[forms]![frmCustomersSearch]![EmailSearch] & "*"
OR IS NULL));

Here is an example that you can download, which might be helpful for you:

http://home.comcast.net/~tutorme2/samples/formcriteriaquerytest.zip


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________